Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2D1eHx16235 for ; Mon, 12 Mar 2001 20:40:17 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f29D0Ix56250 for ; Fri, 9 Mar 2001 08:00:18 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f29CZx327305 for pgsql-sql@postgresql.org; Fri, 9 Mar 2001 07:35:59 -0500 (EST) (envelope-from news) From: "david morgan" X-Newsgroups: comp.databases.postgresql.sql Subject: error joining 2 views containing GROUP BYs Date: Fri, 9 Mar 2001 12:36:08 -0000 Organization: s4c Lines: 145 Message-ID: <98aine$ql6$1@news.tht.net> Reply-To: "david morgan" X-Complaints-To: scrappy@hub.org X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 To: pgsql-sql@postgresql.org X-Archive-Number: 200103/240 X-Sequence-Number: 1899 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;