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

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

What I expected to see is:

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

CodePudding user response：

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]