This works but is expensive since it calculates X,Y,Z for each subquery.
Only AA needs to be checked in each.
Code: Select all
SELECT 3*b3.bin3 + 2*b2.bin2 + b1.bin1 FROM
(SELECT count(*) AS bin1 FROM `TD` WHERE
`X` = 1 AND
`Y` >= 2 AND
`Z` >= 2 AND
`AA` >= 1 AND `AA` <= 2) b1
JOIN
(SELECT count(*) AS bin2 FROM `TD` WHERE
`X` = 1 AND
`Y` >= 2 AND
`Z` >= 2 AND
`AA` >= 2.01 AND `AA` <= 3) b2
JOIN
(SELECT count(*) AS bin3 FROM `TD` WHERE
`X` = 1 AND
`Y` >= 2 AND
`Z` >= 2 AND
`AA` >= 3.01 AND `AA` <= 4) b3;
Code: Select all
WITH `v` AS
(SELECT `AA` FROM `TD` WHERE
`X` = 1 AND
`Y` >= 2 AND
`Z` >= 2)
SELECT 3*bin3 + 2*bin2 + bin1 FROM
(SELECT count(*) AS bin1 FROM `v` WHERE
`AA` >= 1 AND `AA` <= 2)
JOIN
(SELECT count(*) AS bin2 FROM `v` WHERE
`AA` >= 2.01 AND `AA` <= 3)
JOIN
(SELECT count(*) AS bin3 FROM `v` WHERE
`AA` >= 3.01 AND `AA` <= 4);