I need to pull ages of clients and have results broken down into categories (18-21, 22-35, 36-50, etc).
Using Pentaho Report Designer which is Oracle based.
I can pull a count of specific ages, but want the count to reflect the age ranges, not a single age. Attempting to complete this through a case statement but continually get errors.
Minimal SQL exposure, new to Pentaho/Oracle, new to this website.
--SQL for Age and Count.
SELECT COUNT(*), "CLIENT_TABLE"."AGE"
FROM "CLIENT_TABLE"
GROUP BY "CLIENT_TABLE"."AGE"
ORDER BY "CLIENT_TABLE"."AGE"
--This is my CASE expression.
CASE
WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
WHEN "CLIENT_TABLE"."AGE" > 18 AND <= 21 THEN '19 - 21'
WHEN "CLIENT_TABLE"."AGE" > 21 AND <= 35 THEN '22 - 35'
END AS Age
I have placed the CASE expression in both the SELECT and WHERE clauses, but continually get these two error messages... "FROM keyword not found where expects" & "Missing expression".
Updated code produces error, "not a Group By Expression."
SELECT COUNT(*),
(CASE
WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
END) AS AgeRange
FROM "CLIENT_TABLE"
GROUP BY (CASE
WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
END)
ORDER BY "CLIENT_TABLE"."AGE"
Let's simplify this:
SELECT AGERANGE, COUNT(1) AS CNT FROM
SELECT (CASE
WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
END) AS AgeRange
FROM "CLIENT_TABLE")
GROUP BY AGERANGE
ORDER BY CASE AGERANGE
WHEN 'Under 18' THEN 1
WHEN '18 - 21' THEN 2
WHEN '22 - 35' THEN 3
WHEN '36 - 50' THEN 4
WHEN '51 - 64' THEN 5
WHEN '65+' THEN 6
ELSE 7 END;
Cheers!!