I have a table named users in which I have only two columns:
- Holiday Allowance (
users.holidayallowance) - Remaining Allowance (
users.remaining)
I want to find out ranges of the difference of these two columns, e.g.
+--------------+--------+
| Taken Days | Ranges |
+--------------+--------+
| 0 - 10 | 13 |
| 11 - 20 | 3 |
| 21 - 30 | 7 |
+--------------+--------+
The above table would imply: There are 13 people who have taken 0 - 10 days off, 3 people who have taken 11 -20 days off, 7 who haven't taken 21-30 days off etc.
I have tried out the following query, and I know I'm wrong, so if someone could guide me on this, that would be great. Thank you.
SELECT
count(
CASE
WHEN holidayallowance - remaining < 10 THEN '0-10'
WHEN holidayallowance - remaining >10 and holidayallowance - remaining < 20 THEN '10 - 20'
when holidayallowance - remaining >20 and holidayallowance - remaining <30 THEN '20 - 30'
when holidayallowance - remaining >30 and holidayallowance - remaining <40 then '30 - 40'
END
) AS 'Days Taken Off' FROM `users`