CODEWITHSUNDEEP
sqlsql-serversql-server-2012
tylkonachwile
tylkonachwile

Reputation: 2267

Needed helpful hand with sql query

Hello I've been trying to write query that shows all sum of sales month by month. Data also should be grouped by country and category.

Each country has to present all data in all categories even the data don't exists. I know that I probably need cross join and at least one left join, but for few hours I can't figure out how to do it.

Below I attach tables and desired result to help understand.

Table Product

ProductId | Name   | CountryId | CategoryId
------------------------------------------
    4     |  Ax    |     4     |     3
    5     |  Ball  |     5     |     4

Table Category

CategoryId | Name
-----------------
    3      | Detail
    4      | Hurt

Table Country

CountryId | CountryName
-----------------------
    4     | Germany
    5     | Sweden

Table SaleYear

SaleYearId | Year | ProductId
-----------------------------
      1    | 2018 |    4
      2    | 2018 |    5

Table Sale

SaleId | SaleYearId | Month1 | Month2 | Month3 | Month4
   1   |     1      |   100  | NULL   | NULL   | NULL
   2   |     2      |   NULL | 500    | NULL   | NULL

Desired result should looks like:

CountryId | CategoryId | Year | Month1 | Month2 | Month3 | Month4
    4     |      3     | 2018 | 100    | NULL   | NULL   | NULL
    4     |      4     | NULL | NULL   | NULL   | NULL   | NULL
    5     |      3     | NULL | NULL   | NULL   | NULL   | NULL
    5     |      4     | 2018 | NULL   | 500    | NULL   | NULL

DDL and SAMPLE DATA: http://rextester.com/HHN19990

Upvotes: 0

Views: 66

Answers (1)

sgeddes
sgeddes

Reputation: 62841

If I'm understanding your question correctly, you can use cross join with multiple outer joins:

select c.countryid, 
    cat.categoryid,
    sy.year,
    s.month1, 
    s.month2, 
    s.month3, 
    s.month4
from country c cross join category cat
    left join product p on c.countryid = p.countryid and cat.categoryid = p.categoryid
    left join saleyear sy on p.productid = sy.productid
    left join sale s on sy.saleyearid = s.saleyearid

This will create a cartesian product of all the results in the category and country tables. Your example has 2 in each - 2*2 = 4 results. If however you had 5 in each, you'd receive 25 results.

Upvotes: 1

Related Questions