Counting multiple values while using Group By

Question

I apologize if this question is vague, I'm new to SQL and am having some trouble. I'm trying to find the number of people within each weight group. Count that number and display the number of each class.

I think my issue lies within the THEN expression. Do I need to create a variable for each weight group to compare to?

Thank you

SELECT 
(CASE WHEN pl_weight <180 THEN 1
WHEN pl_weight >=180 AND pl_weight <200 THEN 2
WHEN pl_weight >=200 AND pl_weight <220 THEN 3
ELSE 4 END),
count(1) "Less than 280", count(2)"180-200", count(3)"200-220",count(4)"More than 220"  
FROM dl_player
GROUP BY (CASE WHEN pl_weight <180 THEN 1
WHEN pl_weight >=180 AND pl_weight <200 THEN 2
WHEN pl_weight >=200 AND pl_weight <220 THEN 3
ELSE 4
END));

jpw · Accepted Answer · 2015-02-14 17:37:30Z

1

One way to do this is to use conditional aggregation like this:

SELECT 
    SUM(CASE WHEN "pl_weight" <180 THEN 1 ELSE 0 END) AS "Less than 280",
    SUM(CASE WHEN "pl_weight" >=180 AND "pl_weight" <200 THEN 1 ELSE 0 END) AS "180-200",
    SUM(CASE WHEN "pl_weight" >=200 AND "pl_weight" <220 THEN 1 ELSE 0 END) AS "200-220",
    SUM(CASE WHEN "pl_weight" >=220 THEN 1 ELSE 0 END) AS "More than 220"  
FROM dl_player

Sample result:

| LESS THAN 280 | 180-200 | 200-220 | MORE THAN 220 |
|---------------|---------|---------|---------------|
|             4 |       1 |       3 |             3 |

Sample SQL Fiddle

answered Feb 14, 2015 at 17:37

jpw

45k6 gold badges71 silver badges90 bronze badges

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

2 Comments

user2348621 Over a year ago

Thank you, this helped. I assumed you had to use Group By, so that was causing me problems.

jpw Over a year ago

@user2348621 If my answer help solve your problem please consider marking it as accepted. :)

David Faber · Accepted Answer · 2015-02-14 17:52:05Z

1

COUNT() doesn't work the way you want it to; it counts rows or, more specifically, the # of rows for which the value inside is not NULL. You might do this using conditional aggregation, as in @jpw's answer, or you might do it using a subquery:

SELECT weight, COUNT(*) FROM (
    SELECT CASE WHEN pl_weight <180 THEN "Less than 180"
        WHEN pl_weight >=180 AND pl_weight <200 THEN "180-200"
        WHEN pl_weight >=200 AND pl_weight <220 THEN "200-220"
        ELSE "More than 220" END AS weight
      FROM dl_player
) GROUP BY weight;

By the way, I'm not sure if you noticed, but you have an off-by-one error in your final case. It will return 4, or More than 220, for values of 220 or more.

answered Feb 14, 2015 at 17:52

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Collectives™ on Stack Overflow

Counting multiple values while using Group By

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related