question

shabah99 avatar image
shabah99 asked

Finding Item and brand distribution base on customer master

Please help me to write query on my SQL 2008 database to how in one row the following data 1- Brand, item, Route(Salesman),Division (City), Total Available customer (Per Route),Total Available customer (Per Division), Total Customer purchased the Item (Per Route),Total Customer purchased the Item (Per Division), I have two tables [CustomerMaster and SalesData] as shown below. CustomerMaster -------------- | CODE | NAME | ROUTE | DIVISION | |------|------------|-------|----------| | 1001 | Customer1 | R1 | Div1 | | 1002 | Customer2 | R1 | Div1 | | 1003 | Customer3 | R1 | Div1 | | 1004 | Customer4 | R2 | Div1 | | 1005 | Customer5 | R2 | Div1 | | 1006 | Customer6 | R2 | Div1 | | 1007 | Customer7 | R3 | Div2 | | 1008 | Customer8 | R3 | Div2 | | 1009 | Customer9 | R3 | Div2 | | 1010 | Customer10 | R4 | Div2 | SalesData --------- | CUSTOMERCODE | DATE | ITEM | BRAND | QUANTITY | |--------------|--------------------|------|-------|----------| | 1001 | January, 01 2014 | IT1 | BR1 | 2 | | 1001 | February, 01 2014 | IT2 | BR2 | 5 | | 1003 | January, 01 2014 | IT1 | BR1 | 20 | | 1003 | February, 01 2014 | IT3 | BR1 | 6 | | 1005 | January, 01 2014 | IT1 | BR1 | 5 | | 1005 | February, 01 2014 | IT2 | BR2 | 11 | | 1007 | January, 01 2014 | IT1 | BR1 | 15 | | 1007 | February, 01 2014 | IT2 | BR2 | 7 | | 1007 | March, 01 2014 | IT3 | BR1 | 9 | | 1007 | April, 01 2014 | IT4 | BR2 | 18 | --------------------------------------------------------------- View same table [HERE][1] I need to answer below questions To get the Brand distribution over Route and Division 1-Count of customers that bought a brand per Route and Per Division * ex:BR1 reached 2 customers out of 3 in R1 * ex:BR1 reached 3 customers out of 6 in Div1 To get the Item distribution over Route and Division 1-Count of Customers that bought an item per Route and Per Brand * ex:IT2 reached 1 customers out of 3 in R1 * ex:IT2 reached 2 customers out of 6 in Div1 It will be a great if someone can help me. [1]: http://sqlfiddle.com/#!3/1037e/1
sql-server-2008t-sqlqueryselect
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Homework? What have tried and what hasn't worked?
0 Likes 0 ·

1 Answer

·
Squirrel avatar image
Squirrel answered
this is what you wanted ? select cm.Division, cm.Route, sd.Brand, count(distinct cm.CustomerID) as Brand_Distribution from CustomerMaster cm inner join salesdata sd on cm.CustomerID = sd.CustomerID group by cm.Division, cm.Route, sd.Brand
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shabah99 avatar image shabah99 commented ·
Please check this link: http://sqlfiddle.com/#!3/df2bb/5 I need to have count base on brand from ItemMaster table. check if you can fixed it. Thanks
0 Likes 0 ·
shabah99 avatar image shabah99 commented ·
select brandname,division,route,DivisionTotalCustomersCount=MAX(DivisionTotalCustomersCount),RouteTotalCustomersCount=MAX(RouteTotalCustomersCount),BuyingCustomersCount=SUM(BuyingCustomersCount) FROM ( select i.brandname,c.division,c.route, DivisionTotalCustomersCount=(select COUNT(distinct x.CustomerID) from CustomerMaster x where x.division=c.division), RouteTotalCustomersCount=(select COUNT(distinct x.CustomerID) from CustomerMaster x where x.Route=c.route), BuyingCustomersCount=count(distinct C.CustomerID) from CustomerMaster c left outer join SalesData s on c.CustomerID=s.CustomerID right outer join ItemMaster i on s.item=i.itemcode group by i.brandname,c.division,c.route ) A group by brandname,division,route ORDER BY 1 ) A group by brandname,division,route ORDER BY 1
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
The data in your original question differs from the data you've posted at SQLFiddle. And neither show the expected output. Can you clarify the input data and expected output?
0 Likes 0 ·
Squirrel avatar image Squirrel commented ·
shabah99, what's is the query that you just posted for ? It is the solution to your question ?
0 Likes 0 ·
shabah99 avatar image shabah99 commented ·
DO I need to send another question for the same data? Let me know.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.