Re: aggregating into 2 dimensions?

On Tue, 10 Dec 2002 13:47:22 -0000
"Henshall, Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk>
wrote:

> > I'd like to perform a query that aggregates/counts based on 2 of my
> > columns.

[snip]

> SELECT dpa.dt,dpa.tot AS depa,dpb.tot AS depb,dpc.tot AS depc FROM
> (SELECT dt,count(item) AS tot FROM tbl GROUP BY dt WHERE dep=a) AS dpa,
> (SELECT dt,count(item) AS tot FROM tbl GROUP BY dt WHERE dep=b) AS dpb,
> (SELECT dt,count(item) AS tot FROM tbl GROUP BY dt WHERE dep=c) AS dpc WHERE
> dpa.dt=dpb.dt AND dpa.dt=dpc.dt;

Excellent! Just what I was looking for. The only change required was to put the
"group by" after the "where" and it works like a dream - speedy too (unlike the
other kludgy queries I'd devised)

Thanks much - this will be a useful addition to my bag of tricks.

-Harry