SQL multiple Count Query

Question

I have a table which holds grades of students based on three categories. There are five grades (A,B,C,D,E) per category, so the table looks something like this:

id | cat1 | cat2 | cat3
1     A      B       A
2     D      C       D
3     B      A       E
4     C      B       D

etc

I have a second table that list the grades

grade
  A
  B
  C
  D
  E

I need to be able to run a query on this data so that I can count the number of grades achieved in each category for each grade. Something like this:

    Cat1 | Cat2 | Cat 3
A     1     1      1
B     1     2      0
C     1     1      0
D     1     0      2
E     0     0      1

I have ran the following query, which I know is not correct, but is yielding results close to expected:

SELECT g.grade, COUNT( mb.cat1) , COUNT( mb.cat2) , COUNT( mb.cat3)
FROM markbook mb, grades g
WHERE g.grade = mb.cat1
GROUP BY g.grade

score 2 · Accepted Answer · 2013-03-27 16:10:16Z

2

Try:

SELECT g.grade, 
       COUNT(case mb.cat1 when g.grade then 1 end), 
       COUNT(case mb.cat2 when g.grade then 1 end), 
       COUNT(case mb.cat3 when g.grade then 1 end)
FROM markbook mb
cross join grades g
GROUP BY g.grade

(SQLFiddle here)

edited Mar 27, 2013 at 16:10

answered Mar 27, 2013 at 16:04

user359040

Sign up to request clarification or add additional context in comments.

Comments

Simon at The Access Group · Accepted Answer · 2013-03-27 16:29:26Z

2

While I know an answer has been picked already, the way I would have gone for this would be to use LEFT JOIN, so that if a grade had no matches it would still show up. Example as follows:

SELECT 

  g.grade,
  SUM(CASE mb.cat1 WHEN g.grade THEN 1 ELSE 0 END) AS cat1,
  SUM(CASE mb.cat2 WHEN g.grade THEN 1 ELSE 0 END) AS cat2,
  SUM(CASE mb.cat3 WHEN g.grade THEN 1 ELSE 0 END) AS cat3

FROM grades g
LEFT JOIN markbook mb 
    ON g.grade IN (mb.cat1,mb.cat2,mb.cat3)
GROUP BY g.grade;

(SQL fiddle example, schema borrowed from Mark Bannisters answer : http://sqlfiddle.com/#!2/9b863/1 )

edited Mar 27, 2013 at 16:29

answered Mar 27, 2013 at 16:15

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

2 Comments

user359040 Over a year ago

The use of a cross join in my query ensures that a grade with no matches will still appear in mine, too - see sqlfiddle.com/#!2/9b863/2 .

Simon at The Access Group Over a year ago

I see, I thought it would have behaved more like INNER JOIN. Well, you learn something new every day :) This kind of query has never really cropped up in what I tend to do.

Collectives™ on Stack Overflow

SQL multiple Count Query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related