"Subquery returned more than 1 value" for SELECT subquery

Question

I'm using MS SQL Server Management Studio 2008. I'm having an issue with writing a subquery. This is the entire query as follows:

SELECT DISTINCT 
    FH.ShipDate, AVG(FH.[Dist Freight]) AS [Atlantic Freight Charge], 
    (SELECT DISTINCT [Non-Atlantic Freight Charge]
     FROM 
         (SELECT DISTINCT 
              FH.ShipDate, AVG(FH.[Dist Freight]) AS [Non-Atlantic Freight Charge]
          FROM dbo.vw_FreightHistory AS FH
          WHERE VendorName != 'Atlantic Trucking'
          GROUP BY ShipDate, VendorName) AS [Non-Atlantic Freight Charge])
FROM dbo.vw_FreightHistory as FH
WHERE VendorName = 'Atlantic Trucking'
GROUP BY ShipDate, VendorName
ORDER BY ShipDate

The issue first came up when I added that second subquery. The first subquery did not return any errors but it showed the entire average sum of "Dist Freight" in each ShipDate record rather than the average for only that ShipDate. I wrote the second subquery to try and fix that, but now I'm getting this error:

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

Please let me know if I should clarify anything.

Solution 1

I think what you want is something like this:

 SELECT 
    FH.ShipDate, 
    AVG(CASE 
           WHEN VendorName = 'Atlantic Trucking' 
           THEN FH.[Dist Freight] 
           ELSE NULL 
        END) AS [Atlantic Freight Charge],  
    AVG(CASE 
           WHEN VendorName != 'Atlantic Trucking' 
           THEN FH.[Dist Freight] 
           ELSE NULL 
         END) AS [Non-Atlantic Freight Charge]
FROM 
    dbo.vw_FreightHistory as FH
GROUP BY 
    ShipDate
ORDER BY 
    ShipDate

Solution 2

The problem is you have a subquery returning multiple rows and you're trying to store it as a value in a single row/column combination.

To understand why this is the case, let's look at what the result would look like for the outer query, the 'Atlantic Freight Charge':

Table 1 - Atlantic Freight Charges

ShipDate        Atlantic Freight Charge
01/01/2012      1.00
01/02/2012      1.00
01/03/2012      1.00
01/04/2012      1.00
01/05/2012      1.00

And let's look at what the inner subquery might return:

Table 2 - Non-Atlantic Freight Charges

ShipDate        Non-Atlantic Freight Charge
01/01/2012      2.00
01/02/2012      3.00
01/03/2012      4.00
01/04/2012      5.00
01/05/2012      6.00

Finally, what do the distinct Non-Atlantic Freight Charge rows look like for table 2?

Table 3 - Distinct Non-Atlantic Freight Charges

Non-Atlantic Freight Charge
2.00
3.00
4.00
5.00
6.00

Now, in SQL, you are specifying that you want a report with three columns, based on your SELECT clause. Here's how that lays out:

SELECT DISTINCT 

  FH.ShipDate

, AVG(FH.[Dist Freight]) AS [Atlantic Freight Charge]

, (SELECT DISTINCT [Non-Atlantic Freight Charge]
    FROM 
      (SELECT DISTINCT FH.ShipDate, AVG(FH.[Dist Freight]) AS [Non-Atlantic Freight Charge]
          FROM dbo.vw_FreightHistory AS FH
          WHERE VendorName != 'Atlantic Trucking'
          GROUP BY ShipDate, VendorName) AS [Non-Atlantic Freight Charge])

You see the first column is ShipDate, the second column is Atlantic Freight Charge, and the third column is a query of every distinct Non-Atlantic Freight Charge from an inner subquery.

In order for SQL Server to represent this correctly, imagine trying to put the results of that query in the first table.

So for the first row of Table 1:

ShipDate        Atlantic Freight Charge
01/01/2012      1.00

We need to add a column Non-Atlantic Freight Charge, and we need to store in it the results of the query from Table 3:

| ShipDate      | Atlantic Freight Charge | Non-Atlantic Freight Charge     |
|---------------|-------------------------|---------------------------------|
| 01/01/2012    | 1.00                    | | Non-Atlantic Freight Charge | |
|               |                         | |-----------------------------| |
|               |                         | | 2.00                        | |
|               |                         | | 3.00                        | |
|               |                         | | 4.00                        | |
|               |                         | | 5.00                        | |
|               |                         | | 6.00                        | |
|               |                         | |-----------------------------| |
|---------------------------------------------------------------------------|

Uh oh. We've got a table, inside our table.

That's the problem, we've got one table inside another table!

So there are two solutions to your problem. You should evaluate the performance of each.

The first is to use a feature called Common Table Expressions or CTEs to run two separate queries and join the results.

That query would look like this:

CTE Solution

;  WITH Atlantic AS (
 SELECT FH.ShipDate, AVG(FH.[Dist Freight]) AS [Atlantic Freight Charge]
   FROM dbo.vw_FreightHistory as FH
  WHERE VendorName = 'Atlantic Trucking'
  GROUP BY ShipDate
      )
      , NonAtlantic AS (
 SELECT FH.ShipDate, AVG(FH.[Dist Freight]) AS [Non-Atlantic Freight Charge]
   FROM dbo.vw_FreightHistory as FH
  WHERE VendorName != 'Atlantic Trucking'
  GROUP BY ShipDate
      )
 SELECT COALESCE(Atlantic.ShipDate, NonAtlantic.ShipDate)
      , ISNULL([Atlantic Freight Charge], 0) AS [Atlantic Freight Charge]
      , ISNULL([Non-Atlantic Freight Charge], 0) AS [Non-Atlantic Freight Charge]
   FROM Atlantic
        FULL OUTER JOIN NonAtlantic
          ON Atlantic.ShipDate = NonAtlantic.ShipDate

There are some changes I made which I need to point out:

1. I removed the "Order By", in general, ordering should be done by whatever is consuming the data from your SQL Server, don't tax the server unnecessarily by asking it to order something when your client application can do that just as well.
2. ORDER BY is actually prohibited in common table expressions anyway so I'd have to move that clause to the end.
3. I have split up your query into two parts, Atlantic and NonAtlantic, and used a FULL OUTER JOIN to connect them, so any row that's missing in one will still appear, but it'll appear with a zero. Make sure this is what you want.
4. I use a COALESCE to ensure that in case there is a day with no Atlantic Freight Charges and thus there is no ShipDate corresponding to that day in the Atlantic CTE, then it will use the date from the NonAtlantic CTE.

The way this works is that it connects the two queries like this:

ShipDate        Atlantic Freight Charge | FULL OUTER JOIN |  ShipDate        Non-Atlantic Freight Charge
01/01/2012      1.00                    |                 |  NULL            NULL
01/02/2012      1.00                    |                 |  NULL            NULL
01/03/2012      1.00                    |                 |  NULL            NULL
01/04/2012      1.00                    |                 |  01/03/2012      2.00
01/05/2012      1.00                    |                 |  01/04/2012      3.00
NULL            NULL                    |                 |  01/05/2012      4.00
NULL            NULL                    |                 |  01/06/2012      5.00
NULL            NULL                    |                 |  01/07/2012      6.00

And so the COALESCE and ISNULL allow me to turn that into a single set of data like this:

ShipDate        Atlantic Freight Charge  Non-Atlantic Freight Charge
01/01/2012      1.00                     0.00
01/02/2012      1.00                     0.00
01/03/2012      1.00                     0.00
01/04/2012      1.00                     2.00
01/05/2012      1.00                     3.00
01/05/2012      0.00                     4.00
01/06/2012      0.00                     5.00
01/07/2012      0.00                     6.00

However that likely isn't the best performing solution

It's the easiest to implement, take your two queries, run both of them, and join the results. But SQL Server supports aggregate functions that let you partition the results. You may be interested in looking into the semantics of the OVER Clause in order to learn more about how you could run your report in only a single query. I've implemented queries like that myself, but usually using SUMs, not AVGs. I would provide a possible implementation of a solution with the OVER clause, but it might be a little over-complicated and I'd be worried that I'd mess up averaging the results correctly. Actually, now that I think about it, something like this may work fine:

SELECT FH.ShipDate
     , AVG(CASE WHEN VendorName = 'Atlantic Trucking'  THEN FH.[Dist Freight] ELSE NULL END) AS [Atlantic Freight Charge]
     , AVG(CASE WHEN VendorName != 'Atlantic Trucking' THEN FH.[Dist Freight] ELSE NULL END) AS [Non-Atlantic Freight Charge]
  FROM dbo.vw_FreightHistory as FH
 GROUP BY ShipDate
 ORDER BY ShipDate

But I forget if AVG counts null rows or not.

Anyway, I hope I've both answered your question and helped you understand why your query had a problem.