CODEWITHSUNDEEP
sqlsql-server
Stackedup
Stackedup

Reputation: 760

How to Group By columns created in a CASE statement

How can I group by a column that is generated through a CASE statement in the following scenario:

SELECT COUNT(*),
CASE 
    WHEN heart_rate > 220-30 THEN 'above max'
    WHEN heart_rate > ROUND(0.90 * (220-30), 1) THEN 'above target'
    WHEN heart_rate > ROUND(0.50 * (220-30), 1) THEN 'within target'
    ELSE 'below target'
END AS 'hr_zone'
FROM exercise_logs
GROUP BY 'hr_zone';

With the above I get the following error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

I expect to see two columns Count and hr_zone, where rows are grouped by hr_zone.

Upvotes: 0

Views: 80

Answers (1)

dukethrash
dukethrash

Reputation: 1529

In SQL Server you must group by each field you are selecting. You need to duplicate the CASE statement in the group by.

SELECT COUNT(*),
CASE 
    WHEN heart_rate > 220-30 THEN 'above max'
    WHEN heart_rate > ROUND(0.90 * (220-30), 1) THEN 'above target'
    WHEN heart_rate > ROUND(0.50 * (220-30), 1) THEN 'within target'
    ELSE 'below target'
END AS 'hr_zone'
FROM exercise_logs
GROUP BY 
  CASE 
    WHEN heart_rate > 220-30 THEN 'above max'
    WHEN heart_rate > ROUND(0.90 * (220-30), 1) THEN 'above target'
    WHEN heart_rate > ROUND(0.50 * (220-30), 1) THEN 'within target'
    ELSE 'below target'
  END

Or alternatively remove the group by and do a subselect but this may not be performent:

SELECT COUNT(*), hr_zone
  FROM (
       SELECT
       CASE 
           WHEN heart_rate > 220-30 THEN 'above max'
           WHEN heart_rate > ROUND(0.90 * (220-30), 1) THEN 'above target'
           WHEN heart_rate > ROUND(0.50 * (220-30), 1) THEN 'within target'
           ELSE 'below target'
       END AS 'hr_zone'
       FROM exercise_logs
    ) as hr_zones
GROUP BY hr_zone

Or even use UNION queries with each WHERE

SELECT COUNT(*), 'above max'
FROM exercise_logs
WHERE heart_rate > 220-30

UNION

SELECT COUNT(*), 'above target'
FROM exercise_logs
WHERE heart_rate > ROUND(0.90 * (220-30), 1)

--  etc.

Upvotes: 3

Related Questions