Aaron M Aaron M - 5 months ago 12
SQL Question

Counting same data from several column values

i am using sql server 2014.

I have five columns for responses different question, however the different users can possible response the same for different questions.

Getting individual column counts I can do.But how can I get a total of same responses/values from the 5 columns.

Column A Column B Column C Column D Column E
x x y z aa
y z 0 0 0
0 x x 0 x
aa bb 0 x aa


etc

thanks

Answer

You could use the union all operator to get all the values as a single column, and then apply a group by clause:

SELECT   val, COUNT(*)
FROM     (SELECT a AS val FROM mytable
          UNION ALL
          SELECT b AS val FROM mytable
          UNION ALL
          SELECT c AS val FROM mytable
          UNION ALL
          SELECT d AS val FROM mytable
          UNION ALL
          SELECT e AS val FROM mytable) t
GROUP BY val