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
