I want to search a database for people in age range.
WITH age_group AS
(
SELECT 'A' num, 4 start_age, 99 end_age, 2 quantity FROM dual
UNION ALL SELECT 'A', 0, 3, 2 FROM dual
UNION ALL SELECT 'A', 0, 99, 3 FROM dual
UNION ALL SELECT 'B', 4, 18, 2 FROM dual
UNION ALL SELECT 'B', 3, 17, 1 FROM dual
UNION ALL SELECT 'B', 4, 99, 1 FROM dual
UNION ALL SELECT 'B', 4, 99, 3 FROM dual
)
SELECT * FROM
(
SELECT
num
, SUM(a18) AS sum18
, SUM(a11) AS sum11
, SUM(quantity) AS max_quantity
FROM
(
SELECT
num
, IF(start_age <= 18 AND 18 <= end_age , quantity, 0) AS a18
, IF(start_age <= 11 AND 11 <= end_age , quantity, 0) AS a11
, quantity
FROM age_group
) AS age_sum
GROUP BY num
) AS age
WHERE sum18 >= 5 AND sum11 >= 2
age_group defines the age group and the number of people allowed to enter.
Room A can accommodate 2 people aged 4 to 99, 2 people aged 0 to 3, and 3 people aged 0 to 99.
5 people aged 18 and 2 people aged 11 cannot enter Room A.
5 people aged 18 and 2 people aged 2 can enter room A.
Room B can accommodate 2 people aged 4 to 18, 1 person aged 3 to 17, 1 person aged 4 to 99, and 3 people aged 4 to 99.
5 people aged 18 and 2 people aged 11 can enter Room B.
5 people aged 18 and 1 person aged 17 can enter Room B.
1 people aged 18 and 1 person aged 2 cannot enter Room B.
That query statement is a condition for searching room A.
App-generated target age criteria ("IF (start_age <= 18 AND 18 <= end_age, quantity, 0) AS a18 ..", "sum18>= 5 AND sum11>= 2").
However, if both "18" and "11" match, they are counted at both 11 and 18 giving incorrect results.
Incorrect query result. 5 people aged 18 and 2 people aged 11 can enter Room A.
However, cannot actually enter.
