Compute with Sub-Query
-
Hi,
This is a simplified example using the Northwind database. For the life of me, I can't figure out how to get the sum of the OrderCount using a compute clause.
Here is the query I would like to execute:
Select
C.CompanyName
, (Select Count(0) From Orders Where CustomerId = C.CustomerId) OrderCount
From
Customers C
Order By
OrderCount
Compute
Sum(OrderCount)
I've experimented, altering the expression in the Sum function, to no avail.
Because there is a foreign key constraint between customers and orders, I think I can assert that the Sum in the compute clause will always return the number of rows in the Orders table. However, this is a contrived example, and I'm trying to get this approach to work on another pair of tables where there are orphaned children.
I originally wrote the above query much differently, grouping on OrderId, but that resulted in only showing customers who had orders. I need to show Parents who have no children.
Any assistance or advice is appreciated!!!
Richard
-
Hi,
use this Query, I hope your problem is soved :
select a.CompanyName,a.OrderCount from
(Select
C.CompanyName
, (Select Count(0) From Orders Where CustomerId = C.CustomerId) OrderCount
From
Customers C
) a
Order By
OrderCount
Compute
Sum(OrderCount)
Thanks...Amit Gupta
-
There is no need to perform a compute or count of any kind if you are looking for parent rows without children. You need an outer join. This will give you all company names without orders:
Select C.CompanyName
From Customers C
LEFT JOIN Orders O
ON C.CustomerID = O.CustomerID
WHERE O.CustomerID IS NULL
-
Hi Amit,
I re-formatted your query a bit, and it works perfectly.
Thank you for your help!
Have a great weekend!
Richard
The re-formatted query:
Select
S.CompanyName, S.OrderCount
From
(
Select
C.CompanyName
, (Select Count(0) From Orders Where CustomerId = C.CustomerId) OrderCount
From
Customers C
) S
Order By
OrderCount
Compute
Sum(OrderCount)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply