pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mauricio Carvalho de Oliveira <carvalho@dt.fee.unicamp.br>
To: pgsql-sql@hub.org
Subject: Problem with HAVING clause
Date: Mon, 22 Feb 1999 16:49:26 -0300
Message-ID: <36D1B4C6.9F388890@dt.fee.unicamp.br> (raw)
References: <199902220400.XAA82447@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






view thread (2+ messages)  latest in thread

Message-ID: <36D1B4C6.9F388890@dt.fee.unicamp.br>
Permalink:  ../36D1B4C6.9F388890@dt.fee.unicamp.br/
Also on:    postgresql.org/message-id/36D1B4C6.9F388890@dt.fee.unicamp.br

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: carvalho@dt.fee.unicamp.br, pgsql-sql@hub.org
  Subject: Re: Problem with HAVING clause
  In-Reply-To: <36D1B4C6.9F388890@dt.fee.unicamp.br>
  References: <199902220400.XAA82447@hub.org> <36D1B4C6.9F388890@dt.fee.unicamp.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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