I have a big table with row that each have a location id (location_id). Down the location_id column there are multiple instances of the same value. What I want to do is count how many times each value appears and then place that value into a "bucket"
I tried this, but ended up with everything in the "500+" bucket.
SELECT
CASE WHEN count(location_id) > 1 AND count(location_id) <= 25 THEN '1-25'
WHEN count(location_id) > 25 AND count(location_id) <= 500 THEN '26-500'
WHEN count(location_id) > 500 THEN '500+'
ELSE 'nothing'
end as bucket,
count(*) as Column1
FROM myTable
What am I doing wrong here?
You want two levels of aggregation:
SELECT (CASE WHEN cnt <= 25 THEN '1-25'
WHEN cnt <= 500 THEN '26-500'
ELSE '500+'
END) as bucket, COUNT(*) as numlocations, SUM(cnt) as numTotal
FROM (SELECT location_id, count(*) as cnt
FROM myTable
GROUP BY location_id
) l
GROUP BY bucket
ORDER BY MIN(cnt);
Try this:-
Select
CASE WHEN #_cnt >= 1 AND #_cnt<= 25 THEN '1-25'
WHEN #_cnt > 25 AND #_cnt <= 500 THEN '26-500'
WHEN #_cnt > 500 THEN '500+'
ELSE 'nothing'
end as bucket,
count(*) as column1
from
(
Select location_id, count(*) as #_cnt
from
myTable
group by location_id
) a
group by bucket
Thanks:-)