conditional COUNT in MySQL

[gavwvin]

I've got a big query where I SELECT SUM(column) which is grouped by another column... I also want to select the number of rows that equal a certain value. I don't want to exclude those not matching from the sum function (so its not just a WHERE clause) I just want to COUNT if they are equal to a particular value (rather like COUNTIF in excel). Is there a built in SQL function for this? Otherwise I'll have to do a separate query for each one and do mysql_num_rows, which would take extra time.

Thanks

[r937]

lost in space

could you give an example of what you're doing and what you want?

[swampBoogie]

sum(case when <yourCondition> then <yourColumn> else 0 end)

[gavwvin]

thanks swampboogie, works fine as I want to count when the value is one... but if I need any other number I just have to divide by that number at the end to get the real amount.
e.g. sum(case when column=3 then column/3 else 0 end) gives the number of rows which have the value 3

[r937]

could you give an example of what you're doing and what you want?

[Powerlord]

I've got a big query where I SELECT SUM(column) which is grouped by another column... I also want to select the number of rows that equal a certain value. I don't want to exclude those not matching from the sum function (so its not just a WHERE clause) I just want to COUNT if they are equal to a particular value (rather like COUNTIF in excel). Is there a built in SQL function for this? Otherwise I'll have to do a separate query for each one and do mysql_num_rows, which would take extra time.

Thanks

MySQL does have an IF statement, and it looks like it can be used in COUNT statements, like so:

SELECT COUNT(IF(column=3,1,NULL)) AS Count
FROM table GROUP BY othercolumn

I used the number 3 as an example.

I haven't actually tested this SQL, though.

Edit: Mysql's IF() works like this:
IF(expression, return this if expression is true, return this if expression is false)

[r937]

CASE would be better, because it's standard sql, and is portable to other databases

SELECT COUNT(case when column=3 then 937 else NULL end)) AS Count
FROM table GROUP BY othercolumn

[gavwvin]

Thanks everyone, I've learnt something today!