pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Carl van Tast <vanTast@Pivot.at>
To: pgsql-sql@postgresql.org
Subject: Re: group by weirdness
Date: Sat, 15 Sep 2001 00:26:01 +0200
Message-ID: <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com> (raw)
References: <3B9D72B6.5050600@selectacast.net>

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




view thread (7+ messages)  latest in thread

Message-ID: <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com>
Permalink:  ../dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com/
Also on:    postgresql.org/message-id/dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com

 · 

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: vanTast@Pivot.at
  Subject: Re: group by weirdness
  In-Reply-To: <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com>
  References: <3B9D72B6.5050600@selectacast.net> <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com>

* 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