x

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

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.

more ▼

asked Jul 02, 2014 at 06:40 AM in Default

avatar image

shabah99
61 3 3 7

Homework? What have tried and what hasn't worked?

Jul 02, 2014 at 09:12 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jul 02, 2014 at 09:12 AM

avatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2175
x1089
x449
x158

asked: Jul 02, 2014 at 06:40 AM

Seen: 768 times

Last Updated: Jul 02, 2014 at 01:59 PM

Copyright 2017 Redgate Software. Privacy Policy