Rainfall ranged series for each category in PostgresSQL

Question

We have POPULATION and AGE_GROUP tables.

Table : POPULATION

Has entries as follows.

village	age_group	male_count	female_count
A	0-4	4	2
A	5-9	3	4
A	10-14	4	9
A	15-19	8	6
A	25-29	8	6

Like this we have entries for each age group (few age groups are missing, like missing 20-24) for multiple villages called A, B, C, D etc.

Table : AGE_GROUP

Has entries as follows

age_group	min_age	max_age
0-4	0	4
5-9	5	9
10-14	10	14
15-19	15	19
20-24	20	24
25-29	25	29
30-34	30	34
35-39	35	39
40-44	40	44
45-49	45	49
50-54	50	54
55-59	55	59
60-64	60	64
65-69	65	69
70-74	70	74
75-79	75	79
80-84	80	84
85-89	85	89
90-94	90	94
95-99	95	99
100 above	100	150

No stats available for few age groups from each village. Requirements is to show zero for the age age groups, which are not present in that respective village in postgres query.

In case of single village, we can left join POPULATION table with AGE_GROUP table and can use COALESCE() function to fill the missing stats as follows.

SELECT
        village, 
        age_group, 
        COALESCE(male_count, 0) AS male_count, 
        COALESCE(female_count, 0) AS female_count 
FROM 
        AGE_GROUP ag
LEFT JOIN
        POPULATION p
ON 
        p.age_group = ag.age_group

The above query works only, if we have single village (assuming to use COALESEC(village, 'A') as village). But, we have multiple villages and in each village different age groups are missing.

Answer 1

You can first to cross join all vilages with the ag table

CREATE TABLE POPULATION (
  "village" VARCHAR(1),
  "age_group" VARCHAR(5),
  "male_count" INTEGER,
  "female_count" INTEGER
);

INSERT INTO POPULATION
  ("village", "age_group", "male_count", "female_count")
VALUES
  ('A', '0-4', '4', '2'),
  ('A', '5-9', '3', '4'),
  ('A', '10-14', '4', '9'),
  ('A', '15-19', '8', '6'),
  ('A', '25-29', '8', '6'),
    ('B', '0-4', '4', '2'),
  ('B', '5-9', '3', '4'),
  ('B', '60-64', '4', '9'),
  ('B', '15-19', '8', '6'),
  ('B', '25-29', '8', '6');

CREATE TABLE AGE_GROUP (
  "age_group" VARCHAR(9),
  "min_age" INTEGER,
  "max_age" INTEGER
);

INSERT INTO AGE_GROUP
  ("age_group", "min_age", "max_age")
VALUES
  ('0-4', '0', '4'),
  ('5-9', '5', '9'),
  ('10-14', '10', '14'),
  ('15-19', '15', '19'),
  ('20-24', '20', '24'),
  ('25-29', '25', '29'),
  ('30-34', '30', '34'),
  ('35-39', '35', '39'),
  ('40-44', '40', '44'),
  ('45-49', '45', '49'),
  ('50-54', '50', '54'),
  ('55-59', '55', '59'),
  ('60-64', '60', '64'),
  ('65-69', '65', '69'),
  ('70-74', '70', '74'),
  ('75-79', '75', '79'),
  ('80-84', '80', '84'),
  ('85-89', '85', '89'),
  ('90-94', '90', '94'),
  ('95-99', '95', '99'),
  ('100 above', '100', '150');

WITH CTE AS (SELECT DISTINCT "village", ag.*  FROM POPULATION CROSS JOIN AGE_GROUP ag)
SELECT DISTINCT
        ag.village, 
        ag.age_group, 
        COALESCE(male_count, 0) AS male_count, 
        COALESCE(female_count, 0) AS female_count 
FROM 
        CTE ag
LEFT  JOIN 
        POPULATION p
ON p."village" = ag."village" AND 
        p.age_group = ag.age_group

village | age_group | male_count | female_count
:------ | :-------- | ---------: | -----------:
A       | 0-4       |          4 |            2
A       | 100 above |          0 |            0
A       | 10-14     |          4 |            9
A       | 15-19     |          8 |            6
A       | 20-24     |          0 |            0
A       | 25-29     |          8 |            6
A       | 30-34     |          0 |            0
A       | 35-39     |          0 |            0
A       | 40-44     |          0 |            0
A       | 45-49     |          0 |            0
A       | 50-54     |          0 |            0
A       | 55-59     |          0 |            0
A       | 5-9       |          3 |            4
A       | 60-64     |          0 |            0
A       | 65-69     |          0 |            0
A       | 70-74     |          0 |            0
A       | 75-79     |          0 |            0
A       | 80-84     |          0 |            0
A       | 85-89     |          0 |            0
A       | 90-94     |          0 |            0
A       | 95-99     |          0 |            0
B       | 0-4       |          4 |            2
B       | 100 above |          0 |            0
B       | 10-14     |          0 |            0
B       | 15-19     |          8 |            6
B       | 20-24     |          0 |            0
B       | 25-29     |          8 |            6
B       | 30-34     |          0 |            0
B       | 35-39     |          0 |            0
B       | 40-44     |          0 |            0
B       | 45-49     |          0 |            0
B       | 50-54     |          0 |            0
B       | 55-59     |          0 |            0
B       | 5-9       |          3 |            4
B       | 60-64     |          4 |            9
B       | 65-69     |          0 |            0
B       | 70-74     |          0 |            0
B       | 75-79     |          0 |            0
B       | 80-84     |          0 |            0
B       | 85-89     |          0 |            0
B       | 90-94     |          0 |            0
B       | 95-99     |          0 |            0

db<>fiddle here