SQL-запрос для подсчета количества заказов на одного клиента и общей суммы в долларах

Question

У меня две таблицыOrder с колоннами:

OrderID,OrderDate,CID,EmployeeID

А также OrderItem с колоннами:

OrderID,ItemID,Quantity,SalePrice

Мне нужно вернуть CustomerID(CID), количество заказов на каждого клиента и общую сумму для всех клиентов.

Пока у меня есть два отдельных запроса. Один дает мне количество заказов клиентов....

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

А другой дает мне общий объем продаж. У меня проблемы с их объединением...

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

Я делаю это в Access 2010.

Answer 1

Вы бы использовали COUNT(DISTINCT ...) в других движках SQL:

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

Какой доступ к сожалению не поддерживает. Вместо этого вы можете сначала получить суммы заказа в долларах, а затем присоединиться к ним, прежде чем подсчитать количество заказов:

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

Если вам нужно включить клиентов, у которых есть заказы без товаров (необычно, но возможно), измените INNER JOIN в LEFT OUTER JOIN,

Answer 2

Создайте запрос, который использует ваши 2 существующих запроса в качестве подзапросов, и присоедините 2 подзапроса к CID, Определите свой ORDER BY в родительском запросе, а не в подзапросе.

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;