Conditional COUNT in MySQL

Databases
1

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

2

lost in space

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

3 

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

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

5

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

6

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)

7

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

:cool:

8

Thanks everyone, I’ve learnt something today!