pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Problem with HAVING clause 2+ messages / 1 participants [nested] [flat]
* Problem with HAVING clause @ 1999-02-22 19:49 Mauricio Carvalho de Oliveira <carvalho@dt.fee.unicamp.br> 1 sibling, 0 replies; 2+ messages in thread From: Mauricio Carvalho de Oliveira @ 1999-02-22 19:49 UTC (permalink / raw) To: pgsql-sql@hub.org I have created the following two tables: CREATE TABLE tab1 (Id INTEGER, Name TEXT); CREATE TABLE tab2 (Id INTEGER, Attribute INTEGER); populated as: INSERT INTO tab1 VALUES (0, 'name 1'); INSERT INTO tab1 VALUES (1, 'name 2'); INSERT INTO tab1 VALUES (2, 'name 3'); INSERT INTO tab2 VALUES (0, 0); INSERT INTO tab2 VALUES (0, 1); INSERT INTO tab2 VALUES (0, 4); INSERT INTO tab2 VALUES (0, 5); INSERT INTO tab2 VALUES (1, 0); INSERT INTO tab2 VALUES (2, 0); INSERT INTO tab2 VALUES (2, 1); test=> SELECT * FROM tab1; id|name --+------ 0|name 1 1|name 2 2|name 3 (3 rows) test=> SELECT * FROM tab2; id|attribute --+--------- 0| 0 0| 1 0| 4 0| 5 1| 0 2| 0 2| 1 (7 rows) and I would like to list the 'names' which are associated to more than 'N' attributes. For 'N=2' I can get the 'id's by test=> SELECT Id FROM tab2 GROUP BY Id HAVING COUNT(*) > 2; id -- 0 (1 row) so that I expected to be able to use this information in a subquery like: test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING COUNT(*) > 2); ERROR: rewrite: aggregate column of view must be at rigth side in qual which gives me nothing but the strange error above. Although I do not completely understand this message I have rewritten the query so that the aggregate does appear in the right side of the HAVING clause as: test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*)); name ------ name 1 name 2 name 3 (3 rows) but now what I have is something I really do not want: all names! Notice that the "inverted" subquery still gives the correct result: test=> SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*); id -- 0 (1 row) Is this a bug or did I miss some point? Does anybody have some ideas? By now I do the job with the join: test=> SELECT tab1.Name FROM tab1, tab2 WHERE tab1.Id=tab2.Id GROUP BY tab1.Name HAVING COUNT(*) > 2; name ------ name 1 (1 row) I believe this is less efficient than the above since I have to 'join' two tables before applying the HAVING clause. Any comments? TIA P.S.: I am using PostgreSQL v. 6.4.2 -- Mauricio C. de Oliveira mailto:carvalho@dt.fee.unicamp.br http://www.dt.fee.unicamp.br/~carvalho ^ permalink raw reply [nested|flat] 2+ messages in thread
* Problem with HAVING clause @ 1999-02-22 19:53 Mauricio Carvalho de Oliveira <carvalho@dt.fee.unicamp.br> 1 sibling, 0 replies; 2+ messages in thread From: Mauricio Carvalho de Oliveira @ 1999-02-22 19:53 UTC (permalink / raw) To: pgsql-sql@hub.org I have created the following two tables: CREATE TABLE tab1 (Id INTEGER, Name TEXT); CREATE TABLE tab2 (Id INTEGER, Attribute INTEGER); populated as: INSERT INTO tab1 VALUES (0, 'name 1'); INSERT INTO tab1 VALUES (1, 'name 2'); INSERT INTO tab1 VALUES (2, 'name 3'); INSERT INTO tab2 VALUES (0, 0); INSERT INTO tab2 VALUES (0, 1); INSERT INTO tab2 VALUES (0, 4); INSERT INTO tab2 VALUES (0, 5); INSERT INTO tab2 VALUES (1, 0); INSERT INTO tab2 VALUES (2, 0); INSERT INTO tab2 VALUES (2, 1); test=> SELECT * FROM tab1; id|name --+------ 0|name 1 1|name 2 2|name 3 (3 rows) test=> SELECT * FROM tab2; id|attribute --+--------- 0| 0 0| 1 0| 4 0| 5 1| 0 2| 0 2| 1 (7 rows) and I would like to list the 'names' which are associated to more than 'N' attributes. For 'N=2' I can get the 'id's by test=> SELECT Id FROM tab2 GROUP BY Id HAVING COUNT(*) > 2; id -- 0 (1 row) so that I expected to be able to use this information in a subquery like: test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING COUNT(*) > 2); ERROR: rewrite: aggregate column of view must be at rigth side in qual which gives me nothing but the strange error above. Although I do not completely understand this message I have rewritten the query so that the aggregate does appear in the right side of the HAVING clause as: test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*)); name ------ name 1 name 2 name 3 (3 rows) but now what I have is something I really do not want: all names! Notice that the "inverted" subquery still gives the correct result: test=> SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*); id -- 0 (1 row) Is this a bug or did I miss some point? Does anybody have some ideas? By now I do the job with the join: test=> SELECT tab1.Name FROM tab1, tab2 WHERE tab1.Id=tab2.Id GROUP BY tab1.Name HAVING COUNT(*) > 2; name ------ name 1 (1 row) I believe this is less efficient than the above since I have to 'join' two tables before applying the HAVING clause. Any comments? TIA P.S.: I am using PostgreSQL v. 6.4.2 -- Mauricio C. de Oliveira mailto:carvalho@dt.fee.unicamp.br http://www.dt.fee.unicamp.br/~carvalho ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-02-22 19:53 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1999-02-22 19:49 ` Mauricio Carvalho de Oliveira <carvalho@dt.fee.unicamp.br> 1999-02-22 19:53 ` Mauricio Carvalho de Oliveira <carvalho@dt.fee.unicamp.br>
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