cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Count doesn’t work: query wrong!

SOLVED
Highlighted

Count doesn’t work: query wrong!

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 ?!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Count doesn’t work: query wrong!

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

1 REPLY 1

Re: Count doesn’t work: query wrong!

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