Anybody know how to do the following:
file_id (FK/int) - same file id pops up loads of times
statement (varchar) - just some text from the file
status (tinyint 0/1/2) - depending on the statement = 0 or 1 or 2
into a single record for each file_id, with 3 fields - count of 0, count of 1 and count of 2
I've managed to get a count of sorts:
SELECT file_id,`status`, COUNT(*) AS num FROM pl_statements GROUP BY file_id,`status` ORDER BY file_id,`status`
But I'm getting a separate row for each status type (obviously). I've fiddled with multiple counts and tried expressions within counts, but none seem to work. Is what I'm looking for a crosstab query?? Excel is easy with the COUNTIF() function. How the hell do I get it to work in SQL?
Not urgent, but any help much appreciated.