June 30, 2015 at 3:32 pm
I have been tasked with creating a SQL query which does a couple of things and, being a relative new user to SQL, was hoping the community here might be able to help- I don't necessarily need direct answers, but rather help with what I should be looking for to get the right answers! I have three main tables:
Table 1 = Area
Table 2 = Product_Category
Table 3 = Product_Attributes
I'd like to do a count of the number of product_categories, and a sum of the entries in Product_Attributes grouped first by Area, then Product_Category, e.g.
Area1, ProductCat1, Product_attribute1
Area1, ProductCat2, Product_attribute1
Area1, ProductCat2, Product_attribute2
Area1, ProductCat2, Product_attribute3
Area2, ProductCat1, Product_attribute1
Area2, ProductCat1, Product_attribute2
Area3, ProductCat1, Product_attribute8
Area3, ProductCat2, Product_attribute7
Area3, ProductCat3, Product_attribute3
Should give me something like:
Area | productCat | NoOfProductCat | NoOfAttribs
Area1 | ProductCat1 | 1 | 1
Area1 | ProductCat2 | 1 | 3
Area2 | ProductCat1 | 1 | 2
Area3 | ProductCat1 | 1 | 1
Area3 | ProductCat2 | 1 | 1
Area3 | ProductCat3 | 1 | 1
My second challenge is,
I'd like to show the (decimal) ratio of the noofattribs to noofproductcat fields.
My third, and final, challenge (let me know if I should split this into separate questions) - some of my attribs are ages - and I'd like to filter group them (again) so any which are under 30 days old for example are marked as grouped as this month, and a second grouping based on any which are over 30 days (for ease of reference, I'll use the tables above, and call the dates as being either expdate1 for under 30 days or expdate2 for being over 30 days):
Area1, ProductCat1, Product_attribute1, expdate1
Area1, ProductCat2, Product_attribute1, expdate2
Area1, ProductCat2, Product_attribute2, expdate1
Area1, ProductCat2, Product_attribute3, expdate1
Area2, ProductCat1, Product_attribute1, expdate2
Area2, ProductCat1, Product_attribute2, expdate1
Area3, ProductCat1, Product_attribute8, expdate2
Area3, ProductCat2, Product_attribute7, expdate1
Area3, ProductCat3, Product_attribute3, expdate1
should give:
Area | productCat | NoOfProductCat | NoOfAttribs | expdate1 | expdate2
Area1 | ProductCat1 | 1 | 1 | 1 | 0
Area1 | ProductCat2 | 1 | 3 | 2 | 1
Area2 | ProductCat1 | 1 | 2 | 1 | 1
Area3 | ProductCat1 | 1 | 1 | 0 | 1
Area3 | ProductCat2 | 1 | 1 | 1 | 0
Area3 | ProductCat3 | 1 | 1 | 1 | 0
Does that make sense?
Thanks for any help!
July 1, 2015 at 12:50 am
Since you're new here, you should definitely read this article... it explains how to post questions to get the best help.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Once we have some sample data, answering your questions should be pretty easy.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply