Skip to main content
J
jamesbowersmt1-Visitor
1-Visitor
May 24, 2019
Solved

Count doesn’t work: query wrong!

  • May 24, 2019
  • 1 reply
  • 2052 views

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

Best answer by statka

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

S
statka14-AlexandriteAnswer
14-Alexandrite
May 29, 2019

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

Terms & ConditionsAccessibility statement