Postgres Pro
Downloads
Home   >   mailing lists

Thread: GROUP BY on a column which might exist in one of two tables

GROUP BY on a column which might exist in one of two tables

From
Mark Stosberg
Date:
Hello,

I could use some help figuring out a complex "GROUP BY".
As a setup, let's say I have two tables which stores a single "hit" or
"view" in a row

hits hit_id partner_id

views view_id partner_id

There is of course a "partners" table with a "partner_id" column. 

My target result is more like

partner_id
total_views
total_hits

Where the totals are "counts" of the the rows in the hits and views
tables. There should be no rows for partners without hits or views. 

My first attempt at the SQL for this GROUP
COALESCE(hits.partner_id,views.partner_id) which didn't work.

I'm stuck on what to try next. 

Thanks!
    Mark

Re: GROUP BY on a column which might exist in one of two tables

From
hubert depesz lubaczewski
Date:
On Wed, Jun 25, 2008 at 09:50:18AM -0400, Mark Stosberg wrote:
> hits
>   hit_id
>   partner_id
> 
> views
>   view_id
>   partner_id
> 
> There is of course a "partners" table with a "partner_id" column. 
> 
> My target result is more like
> 
> partner_id
> total_views
> total_hits


select   coalesce(h.partner_id, v.partner_id) as partner_id,   coalesce(v.count, 0) as total_views,   coalesce(h.count,
0)as total_hits
 
from   (select partner_id, count(*) from hits group by partner_id) as h   full outer join   (select partner_id,
count(*)from views group by partner_id) as v   on h.partner_id = v.partner_id
 
;

depesz

Re: GROUP BY on a column which might exist in one of two tables

From
Mark Stosberg
Date:
> select
>     coalesce(h.partner_id, v.partner_id) as partner_id,
>     coalesce(v.count, 0) as total_views,
>     coalesce(h.count, 0) as total_hits
> from
>     (select partner_id, count(*) from hits group by partner_id) as h
>     full outer join
>     (select partner_id, count(*) from views group by partner_id) as v
>     on h.partner_id = v.partner_id
> ;
> 

That looks right. Thanks!

Re: GROUP BY on a column which might exist in one of two tables

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Where the totals are "counts" of the the rows in the hits and views
> tables. There should be no rows for partners without hits or views.

How about something like this?:

SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits
FROM (SELECT partner_id, hit_id, NULL AS view_id FROM hitsUNION ALLSELECT partner_id, NULL, view_id FROM views
) AS foo
GROUP BY 1;



- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200806251019
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhiU+8ACgkQvJuQZxSWSsgNiACgmrUWfTv1ZSiiexOKja64p1F8
1hYAn3i+tYoEOIs2NIcSrExlvoyfJE+X
=ryrm
-----END PGP SIGNATURE-----

Re: GROUP BY on a column which might exist in one of two tables

From
Mark Stosberg
Date:
On Wed, 2008-06-25 at 14:20 +0000, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
> 
> 
> > Where the totals are "counts" of the the rows in the hits and views
> > tables. There should be no rows for partners without hits or views.
> 
> How about something like this?:
> 
> SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits
> FROM (
>  SELECT partner_id, hit_id, NULL AS view_id FROM hits
>  UNION ALL
>  SELECT partner_id, NULL, view_id FROM views
> ) AS foo
> GROUP BY 1;

That was very helpful, Greg. 

My new design looks much like this. 
 Mark