Need help with the SQL Subquery
I want to count the total number of order detail rows over all orders a customer has ever 开发者_StackOverflow社区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.
SELECT COUNT(*)
FROM Orders
INNER JOIN Order_Details ON Orders.OrderID = Order_Details.OrderID
WHERE Orders.CustomerID = "123"
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"
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"
The Count(*) is doing the summation for you. Just remove the SUM aggregate from your expression.
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"
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"
精彩评论