pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
error joining 2 views containing GROUP BYs
3+ messages / 2 participants
[nested] [flat]

* error joining 2 views containing GROUP BYs
@ 2001-03-09 12:36 david morgan <david_morgan@s4c.co.uk>
  2001-03-13 04:20 ` Re: error joining 2 views containing GROUP BYs Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: david morgan @ 2001-03-09 12:36 UTC (permalink / raw)
  To: pgsql-sql

Dear Friends,
    I am having a few problems using a select query which joins two views
containing aggregate functions (see query 1 below).  Entering this query in
MS Access through ODBC (linking the tables in) I get the correct result of:

uid    totalanswered        correct    totaltime        weekno
221    5                        2            27                5
223    5                        2            24                5

Using Psql I get the result:

 uid | totalanswered | correct | totaltime | weekno
-----+---------------+---------+-----------+--------
 221 |            10 |      10 |        54 |      5
 223 |            10 |      10 |        48 |      5
(2 rows)

I have read similar posts where people have had problems with views
containing aggregate functions so I realise that there remains work to be
done on this aspect, but my question is:
Does anyone know how I can get this query to work?  Can I make the query be
interpreted in the same way as ODBC?
Is the problem the same as
http://www.postgresql.org/mhonarc/pgsql-sql/2000-11/msg00175.html which Tim
Lane explained the problem "the rewriter effectively
expands them in-line" (Tim Lane)?

Any help or tips would be greatly appreciated.

David Morgan.



drop table Users;
create table Users
(
    UID                           int4 PRIMARY KEY,
    Name                          text,
    Address                       text,
    TelNo                         text,
    EmailAddress                  text,
    FavClub                       text,
    DOB                           date,
    Password                      text,
    Language                      text
);

drop table QuAnswered;
CREATE TABLE "quanswered" (
        "uid" int4 DEFAULT 0 NOT NULL,
        "qid" int4 DEFAULT 0 NOT NULL,
        "aid" int4 DEFAULT 0,
        "tstamp" timestamp DEFAULT "timestamp"('now'::text),
        "ttaken" float4,
        PRIMARY KEY ("uid", "qid")
);

drop table Questions;
CREATE TABLE "questions" (
        "qid" int4 DEFAULT 0 NOT NULL,
        "aid" int4 DEFAULT 0,
        "queng" text,
        "quwel" text,
        "weekno" int2 DEFAULT 0,
        PRIMARY KEY ("qid")
);

INSERT INTO "users" VALUES (221,'james stagg','23 manai
way\015\012cardiff','029
20315273','james_stagg@s4c.co.uk','cardiff','1974-04-15',NULL,'english');
INSERT INTO "users" VALUES (223,'jim','mill
lane','sdkfj','asdgl','rhymmny','199
5-10-01',NULL,'english');

INSERT INTO "questions" VALUES (201,936,'Against which country did Neil
Jenkins
win his first Welsh cap?','201. Yn erbyn pa wlad yr enillodd Neil Jenkins ei
gap
 cyntaf dros Gymru?',5);
INSERT INTO "questions" VALUES (202,366,'Who beat Fiji in the Quarter Finals
of
the 1987 World Cup?','202. Yn erbyn pa wlad y collodd Ffiji yn Rownd Wyth
Olaf C
wpan y Byd 1987?',5);
INSERT INTO "questions" VALUES (203,26,'From which club did Pat Lam join
Northam
pton?','203. I ba glwb yr oedd Pat Lam yn chwarae cyn iddo ymuno gyda
Northampto
n?',5);
INSERT INTO "questions" VALUES (204,821,'In which country was Japan`s scrum
half
 Graeme Bachop born?','204. Ym mha wlad y ganwyd mewnwr Siapan, Graeme
Bachop',5
);
INSERT INTO "questions" VALUES (205,369,'Who is Scotland`s most capped
outside h
alf?','205. Enwch y chwaraewr sydd wedi ymddangos yn safle`r maswr i`r Alban
y n
ifer fwyaf o weithiau?  ',5);


INSERT INTO "quanswered" VALUES (221,201,936,'2001-03-07 10:43:09+00',6);
INSERT INTO "quanswered" VALUES (221,202,366,'2001-03-07 10:43:20+00',8);
INSERT INTO "quanswered" VALUES (221,203,785,'2001-03-07 10:47:15+00',6);
INSERT INTO "quanswered" VALUES (221,204,589,'2001-03-07 10:47:21+00',2);
INSERT INTO "quanswered" VALUES (221,205,257,'2001-03-07 10:47:29+00',5);
INSERT INTO "quanswered" VALUES (223,201,375,'2001-03-07 10:48:14+00',7);
INSERT INTO "quanswered" VALUES (223,202,544,'2001-03-07 10:48:22+00',4);
INSERT INTO "quanswered" VALUES (223,203,26,'2001-03-07 10:48:30+00',6);
INSERT INTO "quanswered" VALUES (223,204,972,'2001-03-07 10:49:42+00',3);
INSERT INTO "quanswered" VALUES (223,205,369,'2001-03-07 10:49:55+00',4);




DROP VIEW all_ans;
CREATE VIEW all_ans as
SELECT  qa.uid, sum(qa.ttaken) as TotalTime, count(qa.aid) as TotalAnswered,
qu.
weekno
FROM quanswered qa, questions qu
WHERE qa.qid=qu.qid
GROUP BY qa.uid, qu.weekno;

DROP VIEW cor_ans;
CREATE VIEW cor_ans AS
SELECT qa.uid, count(qa.uid) AS correct, qu.weekno
FROM questions qu, quanswered qa
WHERE ((qu.aid = qa.aid)
AND (qu.qid = qa.qid))
GROUP BY qa.uid, qu.WeekNo;

Query 1
-------

SELECT all_ans.uid, all_ans.totalanswered, cor_ans.correct,
all_ans.totaltime, all_ans.weekno
FROM all_ans, cor_ans
WHERE all_ans.weekno=     cor_ans.weekno
AND   all_ans.uid=        cor_ans.uid;








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: error joining 2 views containing GROUP BYs
  2001-03-09 12:36 error joining 2 views containing GROUP BYs david morgan <david_morgan@s4c.co.uk>
@ 2001-03-13 04:20 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:04   ` Re: error joining 2 views containing GROUP BYs david morgan <david_morgan@s4c.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2001-03-13 04:20 UTC (permalink / raw)
  To: david morgan <davidmorgan@computerweekly.com>; +Cc: pgsql-sql

"david morgan" <david_morgan@s4c.co.uk> writes:
>     I am having a few problems using a select query which joins two views
> containing aggregate functions (see query 1 below).

I don't think there's any chance of making that work in pre-7.1
Postgres.  Sorry :-(.

As a workaround, consider selecting the grouped data into temp tables
and then joining the temp tables.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: error joining 2 views containing GROUP BYs
  2001-03-09 12:36 error joining 2 views containing GROUP BYs david morgan <david_morgan@s4c.co.uk>
  2001-03-13 04:20 ` Re: error joining 2 views containing GROUP BYs Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-13 15:04   ` david morgan <david_morgan@s4c.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: david morgan @ 2001-03-13 15:04 UTC (permalink / raw)
  To: pgsql-sql

Thanks for your help.
It runs blindlingly fast under beta5 so I'll keep my fingers crossed and
hope it will be stable enough!

Tom Lane wrote in message <17919.984457255@sss.pgh.pa.us>...
>"david morgan" <david_morgan@s4c.co.uk> writes:
>>     I am having a few problems using a select query which joins two views
>> containing aggregate functions (see query 1 below).
>
>I don't think there's any chance of making that work in pre-7.1
>Postgres.  Sorry :-(.






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-13 15:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-09 12:36 error joining 2 views containing GROUP BYs david morgan <david_morgan@s4c.co.uk>
2001-03-13 04:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-13 15:04   ` david morgan <david_morgan@s4c.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox