title: "Subquery returned more than 1 value" for SELECT subquery
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Line 1<br>
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>Please let me know if I should clarify anything.</p>