June 10, 2005 at 7:58 am
Can someone pass along an example of how to return the counts from two table.
order table
order_item table
order_item contains the id from the order table order_item has from zero to many rows for each order row
I need to return the counts of orders that have one or more order_items and I’d like the retuned row to look something like this:
iNumberOfORders iNumberOfOrderItems
-------------------------------------
2 3
This result set would be created because one order has one item and one order has two items.
Thanks in advance for your time
June 10, 2005 at 8:02 am
Select count(distinct OrderID) as Orders, count(*) as Items from dbo.Order_item
(assuming that both columns are non-nullable)
June 10, 2005 at 8:03 am
Have you tried
SELECT COUNT(O1.OrderID), COUNT(O2.ItemID)
FROM dbo.Orders O1
LEFT JOIN dbo.OrderItems O2 ON O1.OrderID = O2.OrderID
Of course this will need to be tweaked but should give you a start...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 10, 2005 at 8:14 am
I thaught of that one too.. but he said he didn't want to have orders with no items (how can that be, that's another questions ). So instead of doing count(*) from left join where somecol not null, I preffered the single table index scan .
June 10, 2005 at 8:14 am
Thanks for the quick replies: I'm embarrassed to say that I forget to mention something. There is a date field on the order table and I only want to count orders (and order items for these orders) where this date field, called order_filled_date, is NULL.
June 10, 2005 at 8:20 am
SELECT COUNT(Distinct O1.OrderID), COUNT(O2.ItemID)
FROM dbo.Orders O1
--inner join flushs orders with no item
INNER JOIN dbo.OrderItems O2 ON O1.OrderID = O2.OrderID
where order_filled_date IS NULL
--not sure, but you might need this
--group by order_filled_date
June 10, 2005 at 8:27 am
Thanks a million Remi and AJ. I'm all set. Remi's last post, built of AJ's post, did the trick.
June 10, 2005 at 8:31 am
HTH.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply