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

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql