CODEWITHSUNDEEP
sqlsql-server
aparna rai
aparna rai

Reputation: 833

Select Count in SQL Server

I have a table [FeedbackPOS] like this:

enter image description here

I want to get How many entry of 5/4/3/2/1 in [Q1],[Q2],[Q3]....[Q11]

Expected output:

Rating | Q1 | Q2 .... Q11
-------+----+-------------
5star  | 2  | 3
4star  | 0  | 0
3star  | 1  | 0
2star  | 0  | 0
1star  | 0  | 0 ..... Q11

Upvotes: 1

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is to unpivot the data and then re-pivot. The following uses apply and conditional aggregation:

select v.stars,
       sum(case when v.q = 'q1' then 1 else 0 end) as q1,
       sum(case when v.q = 'q2' then 1 else 0 end) as q2,
       sum(case when v.q = 'q3' then 1 else 0 end) as q3,
       sum(case when v.q = 'q4' then 1 else 0 end) as q4,
       . . .
from t cross apply
     (values ('q1', q1), ('q2', q2), . . .
     ) v(q, stars)
group by v.stars
order by v.stars;

The need to unpivot the data suggests that you have a poor data model. You should really have a table with a separate row per q.

EDIT:

Here is an alternative method if you want to start with all the ratings:

select v.stars,
       sum(case when t.q1 = v.stars then 1 else 0 end) as q1,
       sum(case when t.q2 = v.stars then 1 else 0 end) as q2,
       sum(case when t.q3 = v.stars then 1 else 0 end) as q3,
       . . .
from (values (1), (2), (3), (4), (5)) v(stars) cross join
     t
group by v.stars
order by v.stars;

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

You can also do like

CREATE TABLE T
(
  Q1 INT,
  Q2 INT,
  Q3 INT
  -- ...
);

INSERT INTO T VALUES
(5, 1, 2),
(5, 4, 5),
(3, 5, 5);

SELECT '5 Stars' Rating,
       (SELECT COUNT(Q1) FROM T WHERE Q1 = 5) Q1,
       (SELECT COUNT(Q2) FROM T WHERE Q2 = 5) Q2,
       (SELECT COUNT(Q3) FROM T WHERE Q3 = 5) Q3
UNION
SELECT '4 Stars' Rating,
       (SELECT COUNT(Q1) FROM T WHERE Q1 = 4) Q1,
       (SELECT COUNT(Q2) FROM T WHERE Q2 = 4) Q2,
       (SELECT COUNT(Q3) FROM T WHERE Q3 = 4) Q3
UNION
SELECT '3 Stars' Rating,
       (SELECT COUNT(Q1) FROM T WHERE Q1 = 3) Q1,
       (SELECT COUNT(Q2) FROM T WHERE Q2 = 3) Q2,
       (SELECT COUNT(Q3) FROM T WHERE Q3 = 3) Q3
UNION
SELECT '2 Stars' Rating,
       (SELECT COUNT(Q1) FROM T WHERE Q1 = 2) Q1,
       (SELECT COUNT(Q2) FROM T WHERE Q2 = 2) Q2,
       (SELECT COUNT(Q3) FROM T WHERE Q3 = 2) Q3
UNION
SELECT '1 Stars' Rating,
       (SELECT COUNT(Q1) FROM T WHERE Q1 = 1) Q1,
       (SELECT COUNT(Q2) FROM T WHERE Q2 = 1) Q2,
       (SELECT COUNT(Q3) FROM T WHERE Q3 = 1) Q3

Upvotes: 0

Related Questions