Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count Distinct 2

Status
Not open for further replies.

SaturnSeven

Programmer
Aug 4, 2005
40
GB
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
 
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
 
One way is to create a query that returns the number of outstanding items by customer (let's call it qryCustOSItem):
Code:
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)
Code:
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:
Code:
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top