pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
group by weirdness
7+ messages / 4 participants
[nested] [flat]

* group by weirdness
@ 2001-09-11 02:11  Joseph Shraibman <jks@selectacast.net>
  0 siblings, 3 replies; 7+ messages in thread

From: Joseph Shraibman @ 2001-09-11 02:11 UTC (permalink / raw)
  To: pgsql-sql

Could someome explain these error messages to me?  Why am I being asked to group by j.id? 
  And why is the subquery worried about ml.oid if ml.oid is used in an aggregate?

Follows: script, then output.


select version();
create table j (id int, created timestamp default current_timestamp, fkey  int);
create table mj (jid int, mid int);
create table ml (jid int, created timestamp default current_timestamp, state int);

insert into j (id, fkey) values (1, 1);
insert into j (id, fkey) values (2, 1);

insert into mj values(1, 1);
insert into mj values(1, 2);
insert into mj values(2, 3);
insert into mj values(2, 4);
insert into mj values(2, 5);

insert into ml(jid, state) values (1, 2);
insert into ml(jid, state) values (1, 2);
insert into ml(jid, state) values (1, 2);
insert into ml(jid, state) values (1, 2);
insert into ml(jid, state) values (1, 11);
insert into ml(jid, state) values (2, 2);
insert into ml(jid, state) values (2, 2);
insert into ml(jid, state) values (2, 11);

select j.id, j.created,   count(mj.mid),
  (select count(ml.oid) where ml.state <> 11),
   (select count(ml.oid) where ml.state IN(2,5) )
FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ;

select j.id, j.created,   count(mj.mid),
  (select count(ml.oid) where ml.state <> 11),
   (select count(ml.oid) where ml.state IN(2,5) )
FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id
group by j.id, j.created;

drop table j;
drop table mj ;
drop table ml;

===================================================================================================

playpen=# select version();
                                version
---------------------------------------------------------------------
  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

playpen=# create table j (id int, created timestamp default current_timestamp, fkey  int);
CREATE
playpen=# create table mj (jid int, mid int);
CREATE
playpen=# create table ml (jid int, created timestamp default current_timestamp, state int);
CREATE
playpen=#
playpen=# insert into j (id, fkey) values (1, 1);
<snip>
playpen=# insert into ml(jid, state) values (2, 11);
INSERT 329676 1
playpen=#
playpen=# select j.id, j.created,   count(mj.mid),
playpen-#  (select count(ml.oid) where ml.state <> 11),
playpen-#   (select count(ml.oid) where ml.state IN(2,5) )
playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ;
ERROR:  Attribute j.id must be GROUPed or used in an aggregate function
playpen=#
playpen=# select j.id, j.created,   count(mj.mid),
playpen-#  (select count(ml.oid) where ml.state <> 11),
playpen-#   (select count(ml.oid) where ml.state IN(2,5) )
playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id
playpen-# group by j.id, j.created;
ERROR:  Sub-SELECT uses un-GROUPed attribute ml.oid from outer query
playpen=#
playpen=# drop table j;
DROP
playpen=# drop table mj ;
DROP
playpen=# drop table ml;
DROP




























-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com




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

* Re: group by weirdness
@ 2001-09-11 04:06  Josh Berkus <josh@agliodbs.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 2 replies; 7+ messages in thread

From: Josh Berkus @ 2001-09-11 04:06 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; pgsql-sql

Joseph,

The subject line could describe a lot of what I see outside my house
every day (I live in San Francisco CA).

> Could someome explain these error messages to me?  Why am I being
> asked to group by j.id? 

Because you've asked the db engine to count on mj.mid.  The parser want
you to be specific about whether the other columns are being aggregated
or not.

>   And why is the subquery worried about ml.oid if ml.oid is used in
> an aggregate?

> playpen=# select j.id, j.created,   count(mj.mid),
> playpen-#  (select count(ml.oid) where ml.state <> 11),
> playpen-#   (select count(ml.oid) where ml.state IN(2,5) )
> playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND
> ml.jid = j.id
> playpen-# group by j.id, j.created;
> ERROR:  Sub-SELECT uses un-GROUPed attribute ml.oid from outer query

Because you're trying to aggregate two aggregates which are sub-selected
in the FROM clause ... a very painful way to not get the results you're
looking for.  Even if you fixed the GROUPing problem, this query
wouldn't parse for other reasons.   For example, the subselects you've
chosen would return the same count for every row, the total of ml.oid in
the database.

Try putting your sub-selects in the FROM clause instead.  (Personally,
I've never found a use for sub-selects in the SELECT clause)

SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount
FROM j, mj,
	(SELECTjid, COUNT(oid) as mcount FROM ml
		WHERE ml.state <> 11 GROUP BY jid) ma1,
	(SELECT jid, COUNT(oid) as mcount FROM ml
		WHERE ml.state in (2,5) GROUP BY jid) ma2
WHERE j.fkey = 1 AND mj.jid = j.id
  AND ma1.jid = j.id AND ma2.jid = j.id
GROUP BY j.id, j.created, ma1.mcount, ma2.mcount;

-Josh Berkus

PS.  Thanks for providing such complete data with your question!


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







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

* Re: group by weirdness
@ 2001-09-13 21:31  Joseph Shraibman <jks@selectacast.net>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Joseph Shraibman @ 2001-09-13 21:31 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql



Josh Berkus wrote:
> Joseph,
> 
> The subject line could describe a lot of what I see outside my house
> every day (I live in San Francisco CA).
> 
> 
>>Could someome explain these error messages to me?  Why am I being
>>asked to group by j.id? 
>>
> 
> Because you've asked the db engine to count on mj.mid.  The parser want
> you to be specific about whether the other columns are being aggregated
> or not.

But they are in seperate tables, so how could it think they could be aggregated together?



-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com




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

* Re: group by weirdness
@ 2001-09-13 21:53  Joseph Shraibman <jks@selectacast.net>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Joseph Shraibman @ 2001-09-13 21:53 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql



Josh Berkus wrote:

> Try putting your sub-selects in the FROM clause instead.  (Personally,
> I've never found a use for sub-selects in the SELECT clause)
> 
> SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount
> FROM j, mj,
> 	(SELECTjid, COUNT(oid) as mcount FROM ml
> 		WHERE ml.state <> 11 GROUP BY jid) ma1,
> 	(SELECT jid, COUNT(oid) as mcount FROM ml
> 		WHERE ml.state in (2,5) GROUP BY jid) ma2
> WHERE j.fkey = 1 AND mj.jid = j.id
>   AND ma1.jid = j.id AND ma2.jid = j.id
> GROUP BY j.id, j.created, ma1.mcount, ma2.mcount;
> 

OK that worked for this simple example, but on my real database the performance was 
horrible, and it didn't work for then there were zero entries in ml (this bites me 
sometimes, when the AND clause keeps things from working as I think they should).  Putting 
the selects in the SELECT solved both problems.  I took out the 'AND ml.jid = j.id' from 
the outer WHERE (would have also excluded cases where there were zero entries in ml) and 
only refrenced ml in the subselect.

Thanks for your help.





-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com




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

* Re: group by weirdness
@ 2001-09-14 15:18  --CELKO-- <joe.celko@trilogy.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 0 replies; 7+ messages in thread

From: joe.celko@trilogy.com @ 2001-09-14 15:18 UTC (permalink / raw)
  To: pgsql-sql

Would this give you what you want? 

 SELECT j.id, j.created, COUNT(mj.mid),
        SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1,
        SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2
   FROM j, mj, ml 
  WHERE j.fkey = 1 
    AND mj.jid = j.id 
    AND ml.jid = j.id;



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

* Re: group by weirdness
@ 2001-09-14 22:26  Carl van Tast <vanTast@Pivot.at>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 1 reply; 7+ messages in thread

From: Carl van Tast @ 2001-09-14 22:26 UTC (permalink / raw)
  To: pgsql-sql

Joseph,

you might want to try:

CREATE VIEW mj1 (jid, cnt) AS
SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid;

CREATE VIEW ml1 (jid, cnt) AS
SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid;

CREATE VIEW ml2 (jid, cnt) AS
SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid;

SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt
FROM j
LEFT JOIN mj1 ON (j.id = mj1.jid)
LEFT JOIN ml1 ON (j.id = ml1.jid)
LEFT JOIN ml2 ON (j.id = ml2.jid)
WHERE j.fkey = 1;

I did not test this with PostgreSQL, but you get the idea.  Probably
PG is even smart enough to handle it all in one:

SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt
FROM j
LEFT JOIN (SELECT jid, COUNT(mid) cnt
           FROM mj
           GROUP BY jid) mj1
     ON (j.id = mj1.jid)
LEFT JOIN (SELECT jid, COUNT(*) cnt
           FROM ml
           WHERE state <> 11
           GROUP BY jid) ml1
     ON (j.id = ml1.jid)
LEFT JOIN (SELECT jid, COUNT(*) cnt
           FROM ml
           WHERE state IN (2, 5)
           GROUP BY jid) ml2
     ON (j.id = ml2.jid)
WHERE j.fkey = 1;

HTH,
 Carl van Tast

On Tue, 11 Sep 2001 02:26:32 +0000 (UTC), jks@selectacast.net (Joseph
Shraibman) wrote:

>Could someome explain these error messages to me?  Why am I being asked to group by j.id? 
>  And why is the subquery worried about ml.oid if ml.oid is used in an aggregate?
>
>Follows: script, then output.
>
>
>select version();
>create table j (id int, created timestamp default current_timestamp, fkey  int);
>create table mj (jid int, mid int);
>create table ml (jid int, created timestamp default current_timestamp, state int);
>
>insert into j (id, fkey) values (1, 1);
>insert into j (id, fkey) values (2, 1);
>
>insert into mj values(1, 1);
>insert into mj values(1, 2);
>insert into mj values(2, 3);
>insert into mj values(2, 4);
>insert into mj values(2, 5);
>
>insert into ml(jid, state) values (1, 2);
>insert into ml(jid, state) values (1, 2);
>insert into ml(jid, state) values (1, 2);
>insert into ml(jid, state) values (1, 2);
>insert into ml(jid, state) values (1, 11);
>insert into ml(jid, state) values (2, 2);
>insert into ml(jid, state) values (2, 2);
>insert into ml(jid, state) values (2, 11);
>
>select j.id, j.created,   count(mj.mid),
>  (select count(ml.oid) where ml.state <> 11),
>   (select count(ml.oid) where ml.state IN(2,5) )
>FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ;
>
>select j.id, j.created,   count(mj.mid),
>  (select count(ml.oid) where ml.state <> 11),
>   (select count(ml.oid) where ml.state IN(2,5) )
>FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id
>group by j.id, j.created;
>
>drop table j;
>drop table mj ;
>drop table ml;
>
>===================================================================================================
>
>playpen=# select version();
>                                version
>---------------------------------------------------------------------
>  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
>(1 row)
>
>playpen=# create table j (id int, created timestamp default current_timestamp, fkey  int);
>CREATE
>playpen=# create table mj (jid int, mid int);
>CREATE
>playpen=# create table ml (jid int, created timestamp default current_timestamp, state int);
>CREATE
>playpen=#
>playpen=# insert into j (id, fkey) values (1, 1);
><snip>
>playpen=# insert into ml(jid, state) values (2, 11);
>INSERT 329676 1
>playpen=#
>playpen=# select j.id, j.created,   count(mj.mid),
>playpen-#  (select count(ml.oid) where ml.state <> 11),
>playpen-#   (select count(ml.oid) where ml.state IN(2,5) )
>playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ;
>ERROR:  Attribute j.id must be GROUPed or used in an aggregate function
>playpen=#
>playpen=# select j.id, j.created,   count(mj.mid),
>playpen-#  (select count(ml.oid) where ml.state <> 11),
>playpen-#   (select count(ml.oid) where ml.state IN(2,5) )
>playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id
>playpen-# group by j.id, j.created;
>ERROR:  Sub-SELECT uses un-GROUPed attribute ml.oid from outer query
>playpen=#
>playpen=# drop table j;
>DROP
>playpen=# drop table mj ;
>DROP
>playpen=# drop table ml;
>DROP




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

* Re: group by weirdness
@ 2001-09-17 18:07  Carl van Tast <vanTast@Pivot.at>
  parent: Carl van Tast <vanTast@Pivot.at>
  0 siblings, 0 replies; 7+ messages in thread

From: Carl van Tast @ 2001-09-17 18:07 UTC (permalink / raw)
  To: pgsql-sql

On Sat, 15 Sep 2001 00:26:01 +0200, I wrote:

> [...]
>CREATE VIEW mj1 (jid, cnt) AS
>SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid;

This should be COUNT(mid) AS cnt ...

> [...]
>I did not test this with PostgreSQL, but you get the idea.

Well, now I did test with PostgreSQL (thanks, Jason Tishler, for your
Cygwin PostgreSQL README!). PG does not support column aliases without
"AS". 

>Probably PG is even smart enough to handle it all in one:

Sure it is. So, Joseph, your solution is:

SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt
FROM j
LEFT JOIN (SELECT jid, COUNT(mid) AS cnt
           FROM mj
           GROUP BY jid) mj1
     ON (j.id = mj1.jid)
LEFT JOIN (SELECT jid, COUNT(*) AS cnt
           FROM ml
           WHERE state <> 11
           GROUP BY jid) ml1
     ON (j.id = ml1.jid)
LEFT JOIN (SELECT jid, COUNT(*) AS cnt
           FROM ml
           WHERE state IN (2, 5)
           GROUP BY jid) ml2
     ON (j.id = ml2.jid)
WHERE j.fkey = 1;

HTH,
 Carl van Tast



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


end of thread, other threads:[~2001-09-17 18:07 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-11 02:11 group by weirdness Joseph Shraibman <jks@selectacast.net>
2001-09-11 04:06 ` Josh Berkus <josh@agliodbs.com>
2001-09-13 21:31   ` Joseph Shraibman <jks@selectacast.net>
2001-09-13 21:53   ` Joseph Shraibman <jks@selectacast.net>
2001-09-14 15:18 ` --CELKO-- <joe.celko@trilogy.com>
2001-09-14 22:26 ` Carl van Tast <vanTast@Pivot.at>
2001-09-17 18:07   ` Carl van Tast <vanTast@Pivot.at>

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