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
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?
