In article <[EMAIL PROTECTED]>,
"Fan, Wellington" <[EMAIL PROTECTED]> writes:
> Hello Listfolk,
> I have a table with a 'category_fk' column and a 'status' column. 'Status'
> has but a tiny handful of known values, kinda like an enum.
> I'd like to form a query that would give me results like:
> category_fk | status=1 | status=2 | status=3 |
> ------------------------------------------------
> toys | 23 | 45 | 0 |
> ------------------------------------------------
> games | 12 | 0 | 0 |
> ------------------------------------------------
> books | 5 | 1 | 3 |
> ------------------------------------------------
> Where the non-fk columns represent the counts of records with that
> category_fk with a certain 'status'
> I've got something close:
> SELECT
> category_fk,
> count(*) as n,
> status
> FROM
> myTable
> GROUP BY
> category_fk,
> status
> But this gives me a record for each category_fk/status.
SELECT category_fk,
sum(case status when 1 then 1 else 0 end) AS 'status=1',
sum(case status when 2 then 1 else 0 end) AS 'status=2',
sum(case status when 3 then 1 else 0 end) AS 'status=3'
FROM myTable
GROUP BY category_fk;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
