Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL Queries – Count total of rows with Case when statement

I will like to count to get the correct number of rows when the [Location] column match a certain value.
Below is my table:

Student Marks Location Date
Kenn 66 UK 09-01-2022
Kenn 89 UK 09-01-2022
Kenn 77 Canada 09-01-2022

Below SQL queries is what I have tried:

  SELECT [Student]
   ,COUNT(CASE WHEN  [Location] = 'UK' THEN [Marks] ELSE 0 END) AS UK
   ,COUNT(CASE WHEN  [Location] = 'Canada' THEN [Marks] ELSE 0 END) AS Canada
  FROM table_name
  GROUP BY [Student]

But the output is

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Student UK Canada
Kenn 3 3

What I expected to see is:

Student UK Canada
Kenn 2 1

Please advise if anything wrong with my SQL queries?
Thank you!!

>Solution :

Use sum instead

SELECT [Student]
   ,SUM(CASE WHEN  [Location] = 'UK' THEN 1 ELSE 0 END) AS UK
   ,SUM(CASE WHEN  [Device] = 'Canada' THEN 1 ELSE 0 END) AS Canada
  FROM table_name
  GROUP BY [Student]
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading