countif in mysql

[diafol]

Anybody know how to do the following:

id (PK/autoincrement)
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.

[Simon Tite]

This should work (I haven't actually tried it yet though)

SELECT file_id, 
	sum(if (status = 0,1,0)) sum0,
	sum(if (status = 1,1,0)) sum1,
	sum(if (status = 2,1,0)) sum2,	
FROM pl_statements
GROUP BY file_id

You don't say which database you are using, the example above should work in MySql, but IF statements are not very standardized between databases. (One other, maybe Oracle or maybe Access, maybe uses IIF instead)

[diafol]

Sorry it's MYSQL - will give it a whirl. Thanks.

//EDIT

Had to take off the last comma and backtick the status field:

SELECT file_id, 
	sum(if (`status`  = 0,1,0)) sum0,
	sum(if (`status` = 1,1,0)) sum1,
	sum(if (`status` = 2,1,0)) sum2 	
FROM pl_statements
GROUP BY file_id

Worked like a charm. Seeing succinct statements like this makes it clear why I will never be a pro *sigh*.
Thanks again.