MySQL query to group data into different ranges
I found a solution for this here, sql query that groups different items into buckets
It works well as long as there is data for all ranges. I want the query to return 0 if there is no data.
So if my table is:
item_name | price
i1 | 2
i2 | 22
i3 | 4
i4 | 26
i5 | 44
i6 | 6
I need output as:
range | number of item
0 - 10 | 3
11 - 20 | 0
21 - 30 | 2
31 - 40 | 0
41 - 50 | 1
The following query does not display the result for counting 0 cases.
select
case when price >= 0 and price <= 10 then " 0 - 10"
when price > 10 and price <= 20 then " 10 - 20"
when price > 20 and price <= 30 then " 20 - 30"
when price > 30 and price <= 40 then " 30 - 40"
when price > 40 and price <= 50 then " 40 - 50"
else "over 50"
end PriceRange,
count(*) as TotalWithinRange
from
YourTable
group by 1
Does anyone have a solution for this?
+3
source to share
3 answers
You need to build an inline table containing all price ranges. Then execute LEFT JOIN
with a derived table based on your query to get the expected result:
SELECT x.PriceRange, COALESCE(TotalWithinRange, 0) AS TotalWithinRange
FROM (
SELECT "0 - 10" AS PriceRange
UNION SELECT "10 - 20"
UNION SELECT "20 - 30"
UNION SELECT "30 - 40"
UNION SELECT "40 - 50"
UNION SELECT "over 50" ) x
LEFT JOIN (
SELECT
CASE when price >= 0 and price <= 10 then "0 - 10"
when price > 10 and price <= 20 then "10 - 20"
when price > 20 and price <= 30 then "20 - 30"
when price > 30 and price <= 40 then "30 - 40"
when price > 40 and price <= 50 then "40 - 50"
else "over 50"
END AS PriceRange,
COUNT(*) as TotalWithinRange
FROM YourTable
GROUP BY 1 ) y ON x.PriceRange = y.PriceRange
+1
source to share