CODEWITHSUNDEEP
sqlpostgresql
Furkan Cigerlioglu
Furkan Cigerlioglu

Reputation: 33

How to use Group By for Range in PostgreSQL?

I have one table like below at AWS Timeseries:

tracking_id measure_value::varchar measure_value::bigint measure_name
269 - 48 age
269 Male - gender

I want to filter it to genders and age range like:

age_range male_count female_count
0-12 15 12
13-17 25 17

I already have a query that:

WITH
genderTable AS (SELECT device_identifier, tracking_id, measure_value::varchar as gender FROM "comp_41"."Visqa18f" WHERE device_identifier='visdemo3' AND measure_name='gender' AND tracking_id IS NOT NULL AND time BETWEEN '2021-03-01 10:30:00' AND '2021-03-01 10:56:00' ORDER BY tracking_id),
ageTable AS (SELECT device_identifier, tracking_id, measure_value::bigint as age FROM "comp_41"."Visqa18f" WHERE device_identifier='visdemo3' AND measure_name='age' AND tracking_id IS NOT NULL 
AND time BETWEEN '2021-03-01 10:30:00' AND '2021-03-01 10:56:00' ORDER BY tracking_id)
SELECT 
SUM(CASE WHEN age > 0 AND age <= 12 AND gender = 'Male' THEN 1 ELSE 0 END) AS "0-12-M",
SUM(CASE WHEN age >= 13 AND age <= 17 AND gender = 'Male' THEN 1 ELSE 0 END) AS "13-17-M",
SUM(CASE WHEN age >= 18 AND age <= 24 AND gender = 'Male' THEN 1 ELSE 0 END) AS "18-24-M",
SUM(CASE WHEN age >= 25 AND age <= 34 AND gender = 'Male' THEN 1 ELSE 0 END) AS "25-34-M",
SUM(CASE WHEN age >= 35 AND age <= 44 AND gender = 'Male' THEN 1 ELSE 0 END) AS "35-44-M",
SUM(CASE WHEN age >= 45 AND age <= 54 AND gender = 'Male' THEN 1 ELSE 0 END) AS "45-54-M",
SUM(CASE WHEN age >= 55 AND gender = 'Male' THEN 1 ELSE 0 END) AS "54-M",

SUM(CASE WHEN age > 0 AND age <= 12 AND gender = 'Female' THEN 1 ELSE 0 END) AS "0-12-F",
SUM(CASE WHEN age >= 13 AND age <= 17 AND gender = 'Female' THEN 1 ELSE 0 END) AS "13-17-F",
SUM(CASE WHEN age >= 18 AND age <= 24 AND gender = 'Female' THEN 1 ELSE 0 END) AS "18-24-F",
SUM(CASE WHEN age >= 25 AND age <= 34 AND gender = 'Female' THEN 1 ELSE 0 END) AS "25-34-F",
SUM(CASE WHEN age >= 35 AND age <= 44 AND gender = 'Female' THEN 1 ELSE 0 END) AS "35-44-F",
SUM(CASE WHEN age >= 45 AND age <= 54 AND gender = 'Female' THEN 1 ELSE 0 END) AS "45-54-F",
SUM(CASE WHEN age >= 55 AND gender = 'Female' THEN 1 ELSE 0 END) AS "54-F"

FROM ageTable, genderTable WHERE ageTable.tracking_id = genderTable.tracking_id

I want to do it with shorter and better optimized query and decided to do it with GROUP BY for age range How can I use GROUP BY for filter on AGE RANGE?

Upvotes: 1

Views: 125

Answers (1)

S-Man
S-Man

Reputation: 23676

This should do it:

demo:db<>fiddle

SELECT
    CASE 
        WHEN age BETWEEN 0 and 12 THEN '0 - 12'
        WHEN age BETWEEN 13 and 17 THEN '13 - 17'
        ...
    END as age_range,
    COUNT(*) FILTER (WHERE gender = 'Male') as male_count,
    COUNT(*) FILTER (WHERE gender = 'Female') as female_count
FROM (
    SELECT
        tracking_id,
        MAX(measure_value_gender) as gender,
        MAX(measure_value_age) as age
    FROM mytable
    GROUP BY tracking_id
) s
GROUP BY age_range

If you don't want to use the FILTER clause, you can, of course, use your SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) construct as well.

Upvotes: 2

Related Questions