Count Distinct 2

[SaturnSeven]

I have 2 tables, Orders & Order Details both related by OrderNo field.

Order Table
OrderNo,CustNo,Date,Seller
S001,100,1/8/06,JT
S002,101,10/8/06,LT
S003,100,5/8/06,JT

Order Details Table
OrderNo,Pos,ItemNo,Status
S001,1,ABC,Complete
S001,2,DEF,OS
S002,1,ABC,OS
S002,2,DEF,OS
S003,1,ABC,OS
I am trying to a unique count of ItemNo and a unique count of OrderNo by CustNo Where Status = "OS" like:-
CustNo,Orders,Items
100,2,2
101,1,2

Many thanks

 

[traingamer]

If the same ItemNo is outstanding on two (or more) different orders for the same customer, should it show up as one ItemNo or more than one ItemNo?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SaturnSeven]

It should shown up as more than one ItemNo

 

[traingamer]

One way is to create a query that returns the number of outstanding items by customer (let's call it qryCustOSItem):

SELECT Order.CustNo, Count(OrderDetail.ItemNo) AS CountOfItemNo
FROM [Order] INNER JOIN OrderDetail ON Order.OrderNo = OrderDetail.OrderNo
GROUP BY Order.CustNo, OrderDetail.Status
HAVING (((OrderDetail.Status)="os"));

which returns
CustNo CountOfItemNo
100 2
101 2
Then a second query getting the number of 'OS' orders (qryOutstanding)

SELECT OD.OrderNo
FROM OrderDetail AS OD
GROUP BY OD.OrderNo, OD.Status
HAVING (((OD.Status)="OS"));

which returns
OrderNo
S001
S002
S003
Then a third query combining the other two:

SELECT Order.CustNo, Count(qryOutstanding.OrderNo) AS CntOrders, qryCustOSItem.CountOfItemNo as CntItems
FROM ([Order] INNER JOIN qryOutstanding ON Order.OrderNo = qryOutstanding.OrderNo) INNER JOIN qryCustOSItem ON Order.CustNo = qryCustOSItem.CustNo
GROUP BY Order.CustNo, qryCustOSItem.CountOfItemNo;

resulting in:
CustNo CntOrders CntItems
100 2 2
101 1 2

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

just as an FYI, you are using the HAVING clause incorrectly (not your fault - Access does it!!)

you only need to use a HAVING clause to filter on an aggregate function. Say you wanted to see total orders by customer:
[tt]
SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customer
[/tt]
this will return:

1 10000
2 20000

if you only want customers who live in California, you would put that in a WHERE clause (similar to just wanting 'OS'):
[tt]
SELECT Customer, SUM(OrderTOtal) From Orders WHERE State = "CA" Group by Customer
[/tt]
If you only want customers who have a OrderTotal sum of greater than 15000 then you would use a HAVING clause:
[tt]
SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customers HAVING Sum(OrderTOtal) > 15000[/tt]

To get customers in California with an OrderTotal > 15000 then:
[tt]
SELECT Customer, SUM(OrderTotal) FROM Orders WHERE State = "CA" GROUP BY Customer HAVING Sum(OrderTotal) > 15000[/tt]

Hope you have a better understandin of the difference between a WHERE clause and the HAVING clause.

Leslie

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[traingamer]

Thanks, Leslie. I did take the lazy way out and just posted what Access gave me by default (while tweaking it along the way).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill