Needed helpful hand with sql query
Clash Royale CLAN TAG#URR8PPP
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
I'm not understanding how you could get year 2018 for data that does not exist? For example, your 2nd result, where does that year come from since it would need to use an outer join and no records would match for countryid 4 and categoryid 4?
– sgeddes
Aug 9 at 19:11
My fault, in 2nd result year of course should be null.I corrected my post.
– tylkonachwile
Aug 9 at 19:13
It would be really helpful if you could turn that sample into ddl (create table statements) and dml (insert statements). The first thing somebody has to do is make that something useful before we can even start working on the problem. But I am a bit nervous about columns like Month1, Month2. That looks a lot like repeating groups which violates 1NF and will make this far more difficult than it needs to be.
– Sean Lange
Aug 9 at 19:16
Of course, I will do it next time. Thank you.
– tylkonachwile
Aug 9 at 19:23
1 Answer
1
If I'm understanding your question correctly, you can use cross join with multiple outer joins:
cross join
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.
cartesian product
category
country
This is what I need, since I said I need summed data, I let myself to correct your answer: select c.countryid, cat.categoryid, sy.year, SUM(s.month1), SUM(s.month2), SUM(s.month3), SUM(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 group by c.countryid, cat.categoryid, sy.year
– tylkonachwile
Aug 9 at 19:21
@tylkonachwile sorry, didn't see your comment about summing. glad you got it working with this though!
– sgeddes
Aug 9 at 19:23
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Stack Overflow is not a free code writing service. You are expected to try to write the code yourself. After doing more research if you have a problem you can post what you've tried with a clear explanation of what isn't working and providing a Minimal, Complete, and Verifiable example. I suggest reading How to Ask a good question and the perfect question. Also, be sure to take the tour.
– Igor
Aug 9 at 19:08