Select Count in SQL Server

Question

I have a table [FeedbackPOS] like this:

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

How about COUNT() and UNION?

Ilyes
– Ilyes

2018-10-27 11:34:22 +00:00
Commented Oct 27, 2018 at 11:34 — Ilyes
– Ilyes, Commented Oct 27, 2018 at 11:34
This number always random? It can be 5,4,3,2,1?

dwir182
– dwir182

2018-10-27 11:38:16 +00:00
Commented Oct 27, 2018 at 11:38 — dwir182
– dwir182, Commented Oct 27, 2018 at 11:38

Gordon Linoff · Accepted Answer · 2018-10-27 12:49:33Z

3

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;

edited Oct 27, 2018 at 12:49

answered Oct 27, 2018 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilyes Over a year ago

order by v.stars DESC; cause the Rating start with 5 Stars

aparna rai Over a year ago

@gordonlinoff thankyou so much for ans its work perfectly but can u do one more favour ? how to get 0 row when supose rating 1 is not available in table actually i want to create atleast 5 rows for 1 to 5 ratings

Ilyes · Accepted Answer · 2018-10-27 11:43:09Z

0

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

answered Oct 27, 2018 at 11:43

Ilyes

15k5 gold badges31 silver badges61 bronze badges

3 Comments

dwir182 Over a year ago

I think it's not efficient when have so many column.. Like the op have..

Ilyes Over a year ago

@dwir182 It's another method beside to what Gordon provide :)

dwir182 Over a year ago

Great! :) @Sami

Collectives™ on Stack Overflow

Select Count in SQL Server

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related