SQL Query ,How to show result in row wise format?

Question

Hello guys I have a sql query that finds sum of columns between some ranges. The query is below

select sum(case when rating_value between 0 and 1.9 then 1 end) as Poor, sum(case when rating_value between 2 and 2.9 then 1 end) as Average, sum(case when rating_value between 3 and 3.9 then 1 end) as Good, sum(case when rating_value between 4 and 5 then 1 end) as Very_Good from rating

which outputs like this

poor | good | verygood | average 39 | 131 | 231 | 552

But i want output like this

Rating |  Count or Value
-----------------------
Poor        31
Good        21
Average     552
very good   231

Hoping for a positive reply

Gordon Linoff · Accepted Answer · 2015-03-31 11:21:29Z

1

Use group by with a case statement:

select (case when rating_value between 0 and 1.9 then 'Poor'
             when rating_value between 2 and 2.9 then 'Average'
             when rating_value between 3 and 3.9 then 'Good'
             when rating_value between 4 and 5 then 'Very_Good'
        end) as grp, count(*)
from rating
group by grp
order by min(rating_value);

answered Mar 31, 2015 at 11:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Arth Parikh Over a year ago

Thank .. Gordon it worked. Sorry cant vote up for this answer bcoz i dont have much reputation.

karthik · Accepted Answer · 2015-03-31 11:25:21Z

0

Use this..

select 
(case when rating_value between 0 and 1.9 then 'Poor' 
case when rating_value between 2 and 2.9 then  'Average'
case when rating_value between 3 and 3.9 then 'Good'
case when rating_value between 4 and 5 then 'Very_Good'
end) as rate, count(*) AS cnt
from rating
group by rate
order by cnt

answered Mar 31, 2015 at 11:25

karthik

2,0641 gold badge13 silver badges5 bronze badges

Comments

Pradip Nadar · Accepted Answer · 2015-03-31 11:37:05Z

0

You can use UNPIVOT as below

SELECT Rating,Value FROM(

select 10  as Poor,20 as Average,45 as Good,15 as Very_Good /*Your Query*/

) AA
UNPIVOT
(Value FOR Rating IN ([Poor],[Average],[Good],[Very_Good])) BB

answered Mar 31, 2015 at 11:37

Pradip Nadar

2821 silver badge4 bronze badges

Comments

Dmitrij Kultasev · Accepted Answer · 2015-03-31 11:37:57Z

0

The same as previous answers, just not using between to make the code with less letters :)

SELECT
    ( CASE WHEN rating_value < 2 THEN 'Poor'
           WHEN rating_value < 3 THEN 'Average'
           WHEN rating_value < 4 THEN 'Good'
           WHEN rating_value <= 5 THEN 'Very_Good'
      END ) AS rate ,
    COUNT(*)
FROM
    rating
GROUP BY
    grp
ORDER BY
    1;

answered Mar 31, 2015 at 11:37

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Collectives™ on Stack Overflow

SQL Query ,How to show result in row wise format?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related