Return the counts from two tables
-
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
-
Select count(distinct OrderID) as Orders, count(*) as Items from dbo.Order_item
(assuming that both columns are non-nullable)
-
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 -
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 . -
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.
-
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
-
Thanks a million Remi and AJ. I'm all set. Remi's last post, built of AJ's post, did the trick.
-
HTH.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply