Different types of correlation query

Question

I have my query and I need to get the same output with a correlated subquery. I'm new in the correlated subqueries, so please help.

The original query:

SELECT Sales.SalesOrderHeader.CustomerID, SUM(Sales.SalesOrderDetail.LineTotal)
FROM Sales.SalesOrderDetail
    INNER JOIN Sales.SalesOrderHeader
    ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
GROUP BY Sales.SalesOrderHeader.CustomerID;

Answer 1

Sorry for all the back and forth in the comments. Using a correlated subquery in the SELECT portion of the query, you could write this, also, as:

SELECT customerID, sum(sumOfLines)
FROM
    (
        SELECT header.CustomerID, 
            (SELECT sum(Detail.LineTotal) FROM Sales.SalesOrderDetail.LineTotal as Detail WHERE Detail.SalesOrderID = header.SalesOrderID) as sumOfLines
        FROM Sales.SalesOrderHeader.CustomerID as header
    ) sub
GROUP BY customerID

This is pretty ugly and is not going to perform faster. There is a fairly good chance your DBMS will choose the same execution path for both version.

---

Update: I updated the above sql to aggregate again by using a subquery so that only unique customerID's come through, since we can't aggregate on a correlated subquery within the query that utilizes the correlated subquery.