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

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: GROUP BY on a column which might exist in one of two tables
Date: 2008-06-25 17:44:24
Message-ID: 1214415864.30599.50.camel@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Marc Mamin 2008-06-26 15:06:14 Re: dynamic events categorization
Previous Message Steve Crawford 2008-06-25 17:12:43 Re: Sequential event query