Re: GROUP BY on a column which might exist in one of two tables - Mailing list pgsql-sql

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