pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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