Count doesn’t work: query wrong!

[jamesbowersmt]

I can't understand because this SQL query doesn't work:

COUNT Department,
IF(Department = 'toys', COUNT(), 0) AS numberOfToys,
IF(Department = 'health', COUNT(), 0) AS numberOfHealth
FROM TABLE;

---

TABLE

Department - Value
toys - A
toys - B
toys - C
health - K
health - F
toys - G
toys - R
toys - W
toys - Q

I'd like to count number of occurrences about both toys record and health ones into 2 columns.

department numberOfToys numberOfHealth

toys 7 0
health 0 2

WHY ?!

[statka · Accepted solution]

Hello @jamesbowersmt,

An easy way to return a count of the number of occurrences of string is as follows:

SELECT Department, count(*) AS Total

FROM TABLE

GROUP BY Department

This should return two rows in the case of your example, one for 'toys' and one for 'health' with their total.  You can get more elaborate with this by introducing case statements in the count as well if you want to go with the two column approach you mentioned in your initial message.  Something like this would work for one of the department types:

SELECT count(CASE Department WHEN 'toys' THEN 1 ELSE NULL END) AS ToysTotal

FROM TABLE

Please review these examples and let us know if you need any further assistance with this.

--Stefan