CODEWITHSUNDEEP
sqlms-access
user1892665
user1892665

Reputation: 73

SQL Query for counting number of orders per customer and Total Dollar amount

I have two tables Order with columns:

OrderID,OrderDate,CID,EmployeeID

And OrderItem with columns:

OrderID,ItemID,Quantity,SalePrice

I need to return the CustomerID(CID), number of orders per customer, and each customers total amount for all orders.

So far I have two separate queries. One gives me the count of customer orders....

SELECT CID, Count(Order.OrderID) AS TotalOrders
FROM [Order]
Where CID = CID 
GROUP BY CID
Order BY Count(Order.OrderID) DESC;

And the other gives me the total sales. I'm having trouble combining them...

SELECT CID, Sum(OrderItem.Quantity*OrderItem.SalePrice) AS TotalDollarAmount
FROM OrderItem, [Order]
WHERE OrderItem.OrderID = [Order].OrderID
GROUP BY CID

I'm doing this in Access 2010.

Upvotes: 7

Views: 133295

Answers (2)

PinnyM
PinnyM

Reputation: 35533

You would use COUNT(DISTINCT ...) in other SQL engines:

SELECT CID, 
       Count(DISTINCT O.OrderID) AS TotalOrders, 
       Sum(OI.Quantity*OI.SalePrice) AS TotalDollarAmount 
FROM [Order] O
INNER JOIN [OrderItem] OI
  ON O.OrderID = OI.OrderID
GROUP BY CID 
Order BY Count(DISTINCT O.OrderID) DESC

Which Access unfortunately does not support. Instead you can first get the Order dollar amounts and then join them before figuring the order counts:

SELECT CID,
       COUNT(Orders.OrderID) AS TotalOrders,
       SUM(OrderAmounts.DollarAmount) AS TotalDollarAmount
FROM [Orders]
INNER JOIN (SELECT OrderID, Sum(Quantity*SalePrice) AS DollarAmount 
      FROM OrderItems GROUP BY OrderID) AS OrderAmounts
  ON Orders.OrderID = OrderAmounts.OrderID
GROUP BY CID
ORDER BY Count(Orders.OrderID) DESC

If you need to include Customers that have orders with no items (unusual but possible), change INNER JOIN to LEFT OUTER JOIN.

Upvotes: 4

HansUp
HansUp

Reputation: 97101

Create a query which uses your 2 existing queries as subqueriers, and join the 2 subqueries on CID. Define your ORDER BY in the parent query instead of in a subquery.

SELECT
    sub1.CID,
    sub1.TotalOrders,
    sub2.TotalDollarAmount
FROM
    (
        SELECT
            CID,
            Count(Order.OrderID) AS TotalOrders
        FROM [Order]
        GROUP BY CID
    ) AS sub1
    INNER JOIN
    (
        SELECT
            CID,
            Sum(OrderItem.Quantity*OrderItem.SalePrice)
                AS TotalDollarAmount
        FROM OrderItem INNER JOIN [Order]
        ON OrderItem.OrderID = [Order].OrderID
        GROUP BY CID
    ) AS sub2
    ON sub1.CID = sub2.CID
ORDER BY sub1.TotalOrders DESC;

Upvotes: 2

Related Questions