SQL counts on a field that is code

Question

I have a table

ANSWERS qId toggle_value 

which writes the value of the HTML switch {Yes, N/A, No, Resolved}

Now I want to count and sum how much yes, nos, nas and allowed are grouped by question id.

For simplicity, I began to build each individual request.

 SELECT qId, count(*) as yes_qty FROM ANSWERS WHERE TOGGLE_VALUE='Yes' GROUP BY qId; SELECT qId, count(*) as na_qty FROM ANSWERS WHERE TOGGLE_VALUE='NA' GROUP BY qId; SELECT qId, count(*) as no_qty FROM ANSWERS WHERE TOGGLE_VALUE='No' GROUP BY qId; SELECT qId, count(*) as resolved_qty FROM ANSWERS WHERE TOGGLE_VALUE='Resolved' GROUP BY qId; 

But I really want it in one request, so that I can iterate over the list and display something like this (aggregation of 14 checklists with 3 questions)

 Q Yes No NA Resolved 1 4 10 0 10 2 14 0 0 0 3 7 0 7 0 

I really don't use strings for the switch value, but the numbers are 1 = yes, 2 = NA, etc .... and wondered if there would be a better table design

 ANSWERS qId yes_value no_value na_value resolved_value 

I would have to reorganize many other things if I changed the deisgn table, so I was hoping that one query would work.

Answer 1

 SELECT qId, SUM(CASE WHEN TOGGLE_VALUE='Yes' THEN 1 ELSE 0 END) AS YesQty, SUM(CASE WHEN TOGGLE_VALUE='No' THEN 1 ELSE 0 END) AS NoQty, SUM(CASE WHEN TOGGLE_VALUE='NA' THEN 1 ELSE 0 END) AS NAQty, SUM(CASE WHEN TOGGLE_VALUE='Resolved' THEN 1 ELSE 0 END) AS ResolvedQty FROM ANSWERS GROUP BY qId 

Answer 2

I like your table design. I personally would solve this using SUM and GROUP BY and using CASE clauses to create new columns.