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