Need help with the SQL Subquery

Question

Hi,

I want to count the total number of order detail rows over all orders a customer has ever had.

This is my query

SELECT SUM(
           (SELECT count(*) 
            FROM dbo.Order_Details 
            WHERE dbo.Order_Details.OrderID = dbo.Orders.OrderID))
    FROM dbo.Orders
    WHERE dbo.Orders.CustomerID = "123"

SQL Server is giving me an error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Any help with this would be appreciated.

Answer 1

You don't need the sum() since the count(*) is already going to give you the total.

SELECT (SELECT count(*) 
    FROM dbo.Order_Details 
    WHERE dbo.Order_Details.OrderID = dbo.Orders.OrderID)
FROM dbo.Orders
WHERE dbo.Orders.CustomerID = "123"

Answer 2

Shouldn't it just be:

SELECT count(*) FROM dbo.Order_Details, dbo.Orders 
 WHERE dbo.Order_Details.OrderID = dbo.Orders.OrderID
 AND dbo.Orders.CustomerID = "123"

Answer 3

The Count(*) is doing the summation for you. Just remove the SUM aggregate from your expression.

Answer 4

I should think something like the following should do what you want:

select count(1) from dbo.order_details d
join dbo.orders o on d.OrderId=o.OrderId
where dbo.orders.CustomerID="123"

Answer 5

SELECT COUNT(*)
FROM Orders
    INNER JOIN Order_Details ON Orders.OrderID = Order_Details.OrderID
WHERE Orders.CustomerID = "123"

Answer 6

The following assumes you have a column in the Order_Details table called OrderDetailID. If not, just substitute for the unique identifier for the order detail record.

SELECT COUNT(DISTINCT OD.OrderDetailID)
FROM Orders O
  LEFT JOIN Order_Details OD on (OD.OrderId = O.OrderId)
WHERE O.CustomerID = "123"