David Sélem Garcia David Sélem Garcia - 6 months ago 10
SQL Question

How to count and group by

As you can see in this image below, I need to count how many number '1' is on every column, the number '1' means that the person interviewed feels secure at Home(AP_4_01),Workplace(AP4_4_02) and so on..

Number 2 = Insecure

Number 3 = Doesn't Apply

Number 9 = Didn't Answer

+----------+----------------------+
| Columns | Numbers of persons |
+----------+----------------------+
| AP4_4_01 | 312 |
| AP4_4_02 | 232 |
| AP4_4_03 | 345 |
| AP4_4_0X | XXX |
+----------+----------------------+





Table with the data

Answer

You just need to use the SUM function on some case statements

SELECT 
    SUM(CASE WHEN AP_4_01 = 1 THEN 1 ELSE 0 END)
   ,SUM(CASE WHEN AP_4_02 = 1 THEN 1 ELSE 0 END)
   ...etc
FROM Table

To get a result set like the one in your question, you will need to use the UNPIVOT function, or you can transpose it in excel.