CODEWITHSUNDEEP
sql
arc000
arc000

Reputation: 893

multiple count conditions with single query

I have a table like below -

Student ID | History | Maths | Geography
1               A        B         B     
2               C        C         E
3               D        A         B
4               E        D         A

How to find out how many students got A in history, B in maths and E in Geography with a single sql query ?

Upvotes: 2

Views: 10139

Answers (3)

Nailgun
Nailgun

Reputation: 4179

You can try to select from multiple select statements

SELECT t1.*, t2.*, t3.* FROM
(SELECT COUNT(*) AS h FROM students WHERE History = 'A') as t1,
(SELECT COUNT(*) AS m FROM students WHERE Maths = 'B') as t2,
(SELECT COUNT(*) AS g FROM students WHERE Geography = 'E') as t3

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

If you want independent counts use:

SELECT SUM(CASE WHEN Condition1 THEN 1 ELSE 0 END) AS 'Condition1'
      ,SUM(CASE WHEN Condition2 THEN 1 ELSE 0 END) AS 'Condition2'
      ,SUM(CASE WHEN Condition3 THEN 1 ELSE 0 END) AS 'Condition3'
FROM YourTable

If you want multiple conditions for one count use:

SELECT COUNT(*)
FROM YourTable
WHERE Condition1
  AND Condition2
  AND Condition3

It sounds like you want multiple independent counts:

SELECT SUM(CASE WHEN History = 'A' THEN 1 ELSE 0 END) AS 'History A'
      ,SUM(CASE WHEN Maths = 'B' THEN 1 ELSE 0 END) AS 'Maths B'
      ,SUM(CASE WHEN Geography = 'E' THEN 1 ELSE 0 END) AS 'Geography E'
FROM YourTable

Upvotes: 3

roman
roman

Reputation: 117571

If you want to get number of students who got A in History in one column, number of students who got B in Maths in second column and number of students who got E in Geography in third then:

select
    sum(case when [History] = 'A' then 1 else 0 end) as HistoryA,
    sum(case when [Maths] = 'B' then 1 else 0 end) as MathsB,
    sum(case when [Geography] = 'E' then 1 else 0 end) as GeographyC
from Table1

If you want to count students who got A in history, B in maths and E in Geography:

select count(*)
from Table1
where [History] = 'A' and [Maths] = 'B' and [Geography] = 'E'

Upvotes: 4

Related Questions