CODEWITHSUNDEEP
mysql
Jojoe Aloha
Jojoe Aloha

Reputation: 31

How to count two field by year

I want to count two field by year

I try

SELECT YEAR(admit_date),COUNT(sub_case),COUNT(SK) 
FROM admit 
WHERE sub_case = "scase001" 
  AND SK = "rsk002"
GROUP BY YEAR(admit_date),sub_case,SK

But it not true

Table

enter image description here

Total

enter image description here

True

2014 1 0
2015 1 1

Upvotes: 1

Views: 43

Answers (2)

jpw
jpw

Reputation: 44871

You could use a conditional aggregation instead like this:

SELECT 
  YEAR(admit_date) AS "admit_year",
  SUM(sub_case = 'scase001') AS "sub_case_count",
  SUM(SK = 'rsk002') AS "sk_count"
FROM admit
GROUP BY YEAR(admit_date)

The expression sub_case = 'scase001' will evaluate to 1 for all matching rows, and summing up the matches is the same as counting them. Using (or abusing) a boolean expression this way might not work in other SQL databases, but it does in MySQL (normally one would use a case when ... then .. end expression).

Upvotes: 1

arnoudhgz
arnoudhgz

Reputation: 1284

You need to remove the sub_case and SK column from the GROUP BY

SELECT YEAR(admit_date),COUNT(sub_case),SUM(CASE WHEN SK="rsk002" THEN 1 ELSE 0 END)
FROM admit 
WHERE sub_case = "scase001" 
GROUP BY YEAR(admit_date)

Upvotes: 1

Related Questions