MySQL CASE WHEN NULL

Question

    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 request. Here are the results:

However, if table.age does not have at least 1 age in the category, it will be simply flat, ignoring this case as a result. For example:

There were no records of age <18 in this dataset. Thus, the age bar "<18" does not appear. How can I do this so that it displays and returns the value 0 ??

Answer 1

You need an age range table to populate the result for records that don't have matching rows. This can be done through the actual table or dynamically generated with this subquery:

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

: http://www.sqlfiddle.com/#!2/7e2a9/10

Answer 2

, .

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

Answer 3

Assuming an AgeCat table that contains your categories.

SELECT c.Cat, COUNT(*) FROM Age a
RIGHT JOIN AgeCat c ON (
(a.age < 18 AND c.Cat = '<18')
OR (a.age BETWEEN 18 AND 24 AND c.Cat = '18-24')
OR (a.age BETWEEN 26 AND 30 AND c.Cat = '25-30')
-- etc.
) GROUP BY c.Cat;