CODEWITHSUNDEEP
sqlpostgresql
i am jack's wasted life
i am jack's wasted life

Reputation: 247

SQL CROSS/LEFT JOIN with some kind of aggregation

I need to join two tables. First contains years, second contains some data related to year. If the name field from the second table has one of all possible year values from first table - this row should be added as is. If not - "new" row with name+year should be generated with null values in all other field. IDK how to be more specific.

tl;dr. Example:

SELECT years.year,
       name,
       salary
FROM
  (SELECT generate_series(2017, 2021) AS YEAR) AS years 
LEFT JOIN
  (SELECT 'Mary' AS name,
          1000 AS salary,
          2017 AS YEAR
   UNION ALL SELECT 'Mary' AS name,
                    1100 AS salary,
                    2018 AS YEAR
   UNION ALL SELECT 'John' AS name,
                    2000 AS salary,
                    2017 AS YEAR
   UNION ALL SELECT 'Kate' AS name,
                    5000 AS salary,
                    2019 AS YEAR) AS dummy_table ON years.year = dummy_table.year;

The result is

 year | name | salary 
------+------+--------
 2017 | John |   2000
 2017 | Mary |   1000
 2018 | Mary |   1100
 2019 | Kate |   5000
 2020 |      |       
 2021 |      |

What I need:

 year | name | salary 
------+------+--------
 2017 | John |   2000
 2017 | Mary |   1000
 2017 | Kate |   0
 2018 | Mary |   1100
 2018 | John |   0
 2018 | Kate |   0
 2019 | Mary |   0
 2019 | John |   0
 2019 | Kate |   5000
...(same zeroes for 2020 and 2021)

Upvotes: 1

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Use a cross join to generate the rows and then a left join:

SELECT years.year, n.name, coalesce(dummy_table.salary, 0)
FROM (SELECT generate_series(2017, 2021) AS YEAR) years CROSS JOIN
     (VALUES ('John'), ('Mary'), ('Kate')
     ) n(name) LEFT JOIN
     ( . . . 
     )
     dummy_table
     ON years.year = dummy_table.year AND n.name = dummy_table.name
ORDER BY years.year, v.name;

Note: You don't have to list the names in dummy_table. You can use:

(select distinct name from dummy_table) n

This works better if you have a real table or CTE.

Upvotes: 1

Related Questions