Possible correlation subqueries

Question

I'm new to subqueries and correlation is now trying to understand a problem.

I have a 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;

I'm trying to rewrite the query using subquery correlation:

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

I have the following error and I don't understand what I need to fix:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Also it will be good if will suggest different options correlation to the original query. Have a good day.

jarlh · Answer 1 · 2016-05-26T08:49:06.850

0

Skip the group by in the subquery, add a correlation instead:

SELECT Sales.SalesOrderHeader.CustomerID,
       (SELECT SUM(Sales.SalesOrderDetail.LineTotal)
        FROM Sales.SalesOrderDetail
        WHERE Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID)
FROM Sales.SalesOrderHeader

Same, using table aliases:

SELECT soh.CustomerID,
       (SELECT SUM(sod.LineTotal)
        FROM Sales.SalesOrderDetail sod
        WHERE sod.SalesOrderID = soh.SalesOrderID)
FROM Sales.SalesOrderHeader soh

Edit:

SELECT DISTINCT soh.CustomerID,
       (SELECT SUM(sod.LineTotal)
        FROM Sales.SalesOrderDetail sod
        WHERE sod.SalesOrderID = soh.SalesOrderID)
FROM Sales.SalesOrderHeader soh

Do SELECT DISTINCT to remove duplicate rows, and return the same result as the GROUP BY version.

edited May 26 '16 at 08:49

answered May 26 '16 at 08:11

jarlh

42,561
8
45
63

It turned 31465 rows 19119 instead. – Vladyslav B. May 26 '16 at 08:16
If interested, you can download the database here. https://msftdbprodsamples.codeplex.com/downloads/get/880661 – Vladyslav B. May 26 '16 at 08:19
31349 rows received. – Vladyslav B. May 26 '16 at 08:53
I understand, there are Sales.SalesOrderHeader rows without references in the Sales.SalesOrderDetail table. The sub-query version is actually more a LEFT JOIN instead of an INNER JOIN. Do you want those rows or not? – jarlh May 26 '16 at 08:56
Yes, they are needed. – Vladyslav B. May 26 '16 at 08:58
Range variables, not table aliases. – onedaywhen Jul 08 '16 at 07:44
@onedaywhen, sorry, but I don't get you here. Are you talking about soh and sod? – jarlh Jul 08 '16 at 07:49
That's right, they are range variables (I was impressed with you correcting someone's use of 'CASE statement). See http://stackoverflow.com/a/1000362/15354. – onedaywhen Jul 08 '16 at 08:03
@onedaywhen, according to the ANSI/ISO SQL standard they are ``'s, but most people don't know what you're talking about if you say that. – jarlh Jul 08 '16 at 08:48

score 0 · Answer 2 · answered May 26 '16 at 08:25

For your understanding, sub-query must return only one value for each row. Your code:

(SELECT SUM(Sales.SalesOrderDetail.LineTotal)
        FROM Sales.SalesOrderDetail
        GROUP BY SalesOrderDetail.SalesOrderID)

will return more then one value because you are grouping by SalesOrderDetail.SalesOrderID and the resultset will have as many rows as different SalesOrderDetail.SalesOrderIDare in the table.

So you have to remove group by clause and it will work. Otherwise if you want to have respective value as per SalesOrderDetail.SalesOrderID then you have to use where clause as in jarlh answer:

(SELECT SUM(SalesOrderDetail.LineTotal)
        FROM SalesOrderDetail
        WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID)

which will give to result-set a value per row which is filtered with SalesOrderDetail.SalesOrderID.

Hope you understand.

Thank you for the clarification of the situation. Any guesses why the different number of rows received? — Vladyslav B., May 26 '16 at 08:37

Possible correlation subqueries

2 Answers2