Sql-server – Reading Data from SQL Server database

sql-server-2008

I want to read data from each cell of database. If the corresponding value is encountered then I have increment a variable count for that corresponding variable.

Consider the following table

Ques1   Ques2   Ques3   Ques4   Ques5
   1      2       3       1        3
   3      4       1       1        2
   2      2       2       1        2  
   1      1       3       4        1
   3      1       4       1        1

So from the above table data, I want to extract the count of each rating. For example the
count of 1 in the above table is 11 and the count of 2 is 6 and the count of 3 is 5 and so
on.. So I have to get the following table as my output

Count1  Count2  Count3  Count4
  11       6       5      3

I have some basic knowledge for incrementing count for that variable but I don't know how to navigate from one cell to another and check its value.

Best Answer

In order to get the count across multiple columns, I'd suggest looking at unpivoting the data. Since you are using SQL Server 2008+, you can use CROSS APPLY to convert your multiple columns into multiple rows. Once the data is in the rows, you can easily count the occurrences. 

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
cross apply
(
  values
    (1, Ques1),
    (2, Ques2),
    (3, Ques3),
    (4, Ques4),
    (5, Ques5)
) c (col, value);

See SQL Fiddle with Demo.

Note, this could also be written using the UNPIVOT function:

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
unpivot
(
  value
  for col in (Ques1, Ques2, Ques3, Ques4, Ques5)
) un;

See SQL Fiddle with Demo

If you don't want to UNPIVOT the data, you could also CROSS JOIN to a virtual table similar to:

select
  sum(case when DATA = 1 then 1 else 0 end) Count1,
  sum(case when DATA = 2 then 1 else 0 end) Count2,
  sum(case when DATA = 3 then 1 else 0 end) Count3,
  sum(case when DATA = 4 then 1 else 0 end) Count4,
  sum(case when DATA = 5 then 1 else 0 end) Count5
from
(
  select
    CASE s.col
      WHEN 'Ques1' THEN Ques1
      WHEN 'Ques2' THEN Ques2
      WHEN 'Ques3' THEN Ques3
      WHEN 'Ques4' THEN Ques4
      WHEN 'Ques5' THEN Ques5
    END AS DATA
  from yourtable
  cross join
  (
    select 'Ques1' AS col UNION ALL 
    select 'Ques2' UNION ALL 
    select 'Ques3' UNION ALL 
    select 'Ques4' UNION ALL 
    select 'Ques5'
  ) s
) s;

See SQL Fiddle with Demo

All versions give the result:

| COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5 |
|--------|--------|--------|--------|--------|
|     11 |      6 |      5 |      3 |      0 |
Related Question