Finding Item and brand distribution base on customer master

Question

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

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.

Answer 1

0

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

answered Jul 02, 2014 at 09:12 AM

Squirrel
2.7k ● 1 ● 4 ● 7

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

Jul 02, 2014 at 09:56 AM shabah99

 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

Jul 02, 2014 at 09:57 AM shabah99

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?

Jul 02, 2014 at 11:09 AM Kev Riley ♦♦

shabah99, what's is the query that you just posted for ? It is the solution to your question ?

Jul 02, 2014 at 12:41 PM Squirrel

DO I need to send another question for the same data? Let me know.

Jul 02, 2014 at 01:59 PM shabah99

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Finding Item and brand distribution base on customer master

SalesData

Follow this question

Related Questions