Menu
SELECT CASE WHEN age IS NULL THEN 'Unspecified'
WHEN age < 18 THEN '<18'
WHEN age >= 18 AND age <= 24 THEN '18-24'
WHEN age >= 25 AND age <= 30 THEN '25-30'
WHEN age >= 31 AND age <= 40 THEN '31-40'
WHEN age > 40 THEN '>40'
END AS ageband,
COUNT(*)
FROM (SELECT age
FROM table) t
GROUP BY ageband
This is my query. These are the results:
However if the table.age doesn't have at least 1 age in a category, it will just flat out ignore that case in the result. Like such:
This data set didnt have any records for age < 18. So the ageband "<18" doesnt show up. How can I make it so it does show up and return a value 0??
857
The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END . For the first syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause until one of them is equal.
The MySQL CASE StatementThe CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
MySQL CASE expression is a control flow structure that allows you to add if-else logic to a query. Generally speaking, you can use the CASE expression anywhere that allows a valid expression e.g., SELECT , WHERE and ORDER BY clauses. The CASE expression has two forms: simple CASE and searched CASE .
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
You need a table of agebands to populate the result for entries that have no matching rows. This can be done through an actual table, or dynamically generated with a subquery like this:
SELECT a.ageband, IFNULL(t.agecount, 0)
FROM (
-- ORIGINAL QUERY
SELECT
CASE
WHEN age IS NULL THEN 'Unspecified'
WHEN age < 18 THEN '<18'
WHEN age >= 18 AND age <= 24 THEN '18-24'
WHEN age >= 25 AND age <= 30 THEN '25-30'
WHEN age >= 31 AND age <= 40 THEN '31-40'
WHEN age > 40 THEN '>40'
END AS ageband,
COUNT(*) as agecount
FROM (SELECT age FROM Table1) t
GROUP BY ageband
) t
right join (
-- TABLE OF POSSIBLE AGEBANDS
SELECT 'Unspecified' as ageband union
SELECT '<18' union
SELECT '18-24' union
SELECT '25-30' union
SELECT '31-40' union
SELECT '>40'
) a on t.ageband = a.ageband
Demo: http://www.sqlfiddle.com/#!2/7e2a9/10
159
I haven't tested it, but this should work.
SELECT ageband, cnt FROM (
SELECT '<18' as ageband, COUNT(*) as cnt FROMT table WHERE age < 18
UNION ALL
SELECT '18-24' as ageband, COUNT(*) as cnt FROMT table WHERE age >= 18 AND age <= 24
UNION ALL
SELECT '25-30' as ageband, COUNT(*) as cnt FROMT table WHERE age >= 25 AND age <= 30
UNION ALL
SELECT '31-40' as ageband, COUNT(*) as cnt FROMT table WHERE age >= 31 AND age <= 40
UNION ALL
SELECT '>40' as ageband, COUNT(*) as cnt FROMT table WHERE age > 40
) as A
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
