Apurv Apurv - 3 months ago 10
SQL Question

Creating Range Buckets of column

I am having one base table named test_table on which I am doing calculation of the age and it is coming as per expectation.

Here is the query I am using for age calculation.

select acol,
DATEDIFF(hour,CONVERT(DATEADD('SECOND', (epoch_time)/1000, DATE '1970-01-01'), DATE), CURRENT_TIMESTAMP()) AS age
from test_table;


But now I want to create different range of age column which will be displayed as a result by modifying the existing query.

Range would be :

1000-2000

2000-3000

3000-4000

ACOL AGE
MAG 1168
MAG 2168
MAG 3168
MAG 1100
MAG 2168
PNB 1672
MUM 1600
MUM 2696
MUM 3696
MUM 1696


Result after successful query has to look like below table.

ACOL 1000-2000 2000-3000 3000-4000
MAG 2 2 1
PNB 1 0 0
MUM 2 1 1


I know that we can do this using select case but not able to build the working query.
can anyone help me in this.

thanks in advance.

Answer

You can use conditional aggregation with a CASE expression:

SELECT t.ACOL,
       SUM(CASE WHEN t.AGE >= 1000 AND t.AGE < 2000 THEN t.AGE ELSE 0 END) AS `1000-2000`,
       SUM(CASE WHEN t.AGE < 3000 THEN t.AGE ELSE 0 END) AS `2000-3000`,
       SUM(CASE WHEN t.AGE < 3000 AND t.AGE < 4000 THEN t.AGE ELSE 0 END) AS `3000-4000`
FROM
(
    SELECT ACOL,
           DATEDIFF(HOUR, CONVERT(DATEADD(SECOND, (epoch_time)/1000, DATE '1970-01-01'), DATE), CURRENT_TIMESTAMP()) AS AGE
    FROM test_table
) t
GROUP BY t.ACOL
Comments