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
Homework? What have tried and what hasn't worked?
Best Answer
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
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
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
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?
shabah99, what's is the query that you just posted for ? It is the solution to your question ?
DO I need to send another question for the same data? Let me know.
