Thread: aggregating into 2 dimensions?

I'd like to perform a query that aggregates/counts based on 2 of my columns.
For example, with a table with 3 columns "date","dept","item", I'd like to get
counts of "item" grouped by "date" and "dept" so that I get a tablular result
such as:

    date    | deptA | deptB | deptC | ...
------------+------------------------------
 2001-05-28 |    10 |   231 |   123 | ...

Basically, something like "select date,dept,count(item) from table group by
date,dept" except giving the results in 2 dimensions, with individual depts as
columns and perhaps a column for a total.

Is this something that can be done with basic SQL (without using functions)?
The number of depts is small and fixed, if that matters.

Thanks,
Harry

Harry wrote:
> I'd like to perform a query that aggregates/counts based on 2 of my
> columns. For example, with a table with 3 columns
> "date","dept","item", I'd like to get
> counts of "item" grouped by "date" and "dept" so that I get a
> tablular result such as:
>
>     date    | deptA | deptB | deptC | ...
> ------------+------------------------------
>  2001-05-28 |    10 |   231 |   123 | ...
>
> Basically, something like "select date,dept,count(item) from table
> group by date,dept" except giving the results in 2 dimensions, with
> individual depts as columns and perhaps a column for a total.
>
> Is this something that can be done with basic SQL (without using
> functions)? The number of depts is small and fixed, if that matters.
>
> Thanks,
> Harry
>
If the number is small and fixed then you could do something like this:

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;

Where dt is the date (Note totally untested)
hth,
- Stuart

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