How can write a query that makes histogram graph rendering easier?
For example, we have 100 million people with ages, we want to draw the histogram/buckets for age 0-10, 11-20, 21-30 etc... What does the query look like?
Has anyone done it? Did you try to connect the query result to google spreadsheet to draw the histogram?
The subquery idea works, as does "CASE WHEN" and then doing a group by:
SELECT SUM(field1), bucket FROM ( SELECT field1, CASE WHEN age >= 0 AND age < 10 THEN 1 WHEN age >= 10 AND age < 20 THEN 2 WHEN age >= 20 AND age < 30 THEN 3 ... ELSE -1 END as bucket FROM table1) GROUP BY bucket
Alternately, if the buckets are regular -- you could just divide and cast to an integer:
SELECT SUM(field1), bucket FROM ( SELECT field1, INTEGER(age / 10) as bucket FROM table1) GROUP BY bucket