Count Customers for an Item 1

[evr72]

Hello,
I am trying to build a query to give me the number of customers that have bought an item.

Here is what my table looks like
Customer Name
Item Number
Qty
Dollars

so I have customer XYZ that has bought item number 001 different times with different quantities over the past 5 years.

I would like to see

Customer Name Item Number Qty Dollars Num Of Cust

XYZ 001 25365 $5689 1

But instead I get this

Customer Name Item Number Qty Dollars Num Of Cust

XYZ 001 25365 $5689 45



45 is the number of times that customer XYZ has bought the item


Here is the SQL statement that I am using

SELECT Customer, item, Sum(qty) AS SumOfqty, Sum(dollars) AS SumOfdollars, Count(customer) AS num_of_customers
FROM mylib
GROUP BY customer, item
HAVING (((item)="001"));

 

[PWise]

think for a second how would like the second line to look

Customer Name Item Number Qty Dollars Num Of Cust

Abc 001 2536 $568 1

what is the point of Num Of Cust it will always be on


perhaps what you want is this query

SELECT  item, Sum(qty) AS SumOfqty, Sum(dollars) AS SumOfdollars, Count(customer) AS num_of_customers
FROM mylib
GROUP BY  item
HAVING (((item)="001"));

 

[evr72]

I did try that before but it gave me the same answer

for example

This is what my table looks like

Customer Name Item Number Qty Dollars
XYZ 001 400 $800 XYZ 001 200 $400 XYZ 001 200 $400 XYZ 001 300 $600 XYZ 001 200 $400 XYZ 001 100 $200 XYZ 001 100 $200


I would like to get

Customer Name Item Number Qty Dollars Number of Customers
XYZ 001 1500 $3000 1

But instead I get

Customer Name Item Number Qty Dollars Number of Customers
XYZ 001 1500 $3000 7

 

[Andrzejek]

Since your num_of_customers will always be 1, try:

SELECT Customer, item, Sum(qty) AS SumOfqty, Sum(dollars) AS SumOfdollars, 1 AS num_of_customers
FROM mylib
GROUP BY customer, item
HAVING (((item)="001"));

Have fun.

---- Andy

 

[evr72]

Andrzejek,

I am not sure how that is going to help. That will put a 1 in every entry even if it is the same customer

 

[PWise]

you ran this qurey

SELECT  item, Sum(qty) AS SumOfqty, Sum(dollars) AS SumOfdollars, Count(customer) AS num_of_customers
FROM mylib
GROUP BY  item
HAVING (((item)="001"));

And got this??
Customer Name Item Number Qty Dollars Number of Customers
XYZ 001 1500 $3000 7

 

[PWise]

you are giving an example of 1 customer and 1 item

how would you like it should look with 2 coustomes and 3 items

coustomer1 bought item 1 and 2
coustomer1 bought item 1 and 3

 

[evr72]

PWise,
yes that was one customer one item

for different customers different items I would like it to look like this

For these customers

customer  item  qty  dollars
xyz       004    100   $200
xyz       001    200   $400
xyz       002    100   $200
xyz       001    200   $400
xyz       001    100   $200
abc       004    100   $200
abc       001    100   $200
abc       001    100   $200
abc       001    100   $200
abc       001    100   $200
dfg       001    300   $600

So for item 001 the 3 customers bought the item
for item 002 only 1 customer bought it
for item 004 2 customers bought it

so the result would look like this

item   qty   dollars  number of customers
001    1400   $2400       3
002    100    $200        1
004    400    $400        2

 

[PWise]

how about

SELECT  item, Sum(qty) AS SumOfqty, Sum(dollars) AS SumOfdollars, cc.num_of_customers
FROM mylib
inner join (Select item ,count(customer) as num_of_customers

            from  mylib
            GROUP BY  item
            ) as cc
on cc.item=mylib.item
GROUP BY  item,cc.num_of_customers
HAVING (((item)="001"));

 

[evr72]

hmm for some odd reason it is sill giving me 4 when it should be 2.
Here is what I tried and seems to work

I created a query where I group the customer and the item, then sum qty and dollars let's call this query1

then create another query from query one and do a count on customers. This gives me the number of customers. seems to work.

I really appreciate all the help!!!

 

[PHV]

Why 2 queries ?
What about this ?

SELECT item, Sum(q) AS qty, Sum(d) AS dollars, Count(*) AS NumOfCustomer
FROM (SELECT Customer,item,Sum(qty) AS q,Sum(dollars) AS d FROM mylib GROUP BY customer,item) D
GROUP BY item

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886