title: GROUP BY in subquery T-SQL
body: <p>I am trying to make a table like this:</p>
  
  <blockquote>
    <p>ProductName |  SalesByDate |  TotalSalesUntilDate</p>
  
  <pre><code>    A     |      5    |      15
  
      B     |     10    |      30
  
      C     |     20    |      25
  
      D     |     18    |      43
  </code></pre>
  </blockquote>
  
  <p>SalesByDate means the number of product sold for each product on the input date and TotalSalesUntilDate indicates the number of product sold for each product from the first date of the month until the input date (example of input date: 17 March 2010)</p>
  
  <p>I wrote this query using subquery:</p>
  
  <pre><code>    select p.ProductName, A.SalesByDate,
        (select(SUM(case when (pd.Date between '01' and @Date) 
        then s.SalesByDate else 0 end)) 
      from Period pd 
      inner join Sales s on pd.TimeID = s.TimeID 
      full join Product p on s.ProductID = p.ProductID) as TotalSalesUntilDate 
      from Product p join 
           (select s.ProductID, pd.Date, s.SalesByDate
            from Period pd join Sales s on pd.TimeID = s.TimeID) A on 
      p.ProductID = A.ProductID where @Date = A.Date
  </code></pre>
  
  <p>but I got the result:</p>
  
  <blockquote>
    <p>ProductName   |     SalesByDate |  TotalSalesUntilDate</p>
  
  <pre><code>     A    |     5     |      113
  
       B    |    10     |      113
  
       C    |    20     |      113
  
       D    |    18     |      113
  </code></pre>
  </blockquote>
  
  <p>which the TotalSalesUntilDate shows the number of product sold from the first date of the month until the input date but for all product without separation for each product.</p>
  
  <p>So when I tried to change the query to like this (adding GROUP BY p.ProductID before "as TotalSalesUntilDate"):</p>
  
  <pre><code>   select p.ProductName, A.SalesByDate,
       (select(SUM(case when (pd.Date between '01' and @Date) 
       then s.SalesByDate else 0 end)) 
     from Period pd 
       inner join Sales s on pd.TimeID = s.TimeID 
       full join Product p on s.ProductID = p.ProductID 
       group by p.ProductID) as TotalSalesUntilDate 
    from Product p join 
       (select s.ProductID, pd.Date, s.SalesByDate
       from Period pd join Sales s on pd.TimeID = s.TimeID) A on 
      p.ProductID = A.ProductID where @Date = A.Date
  </code></pre>
  
  <p>and when I execute this query, I got this error message:</p>
  
  <blockquote>
    <p>"Msg 512, Level 16, State 1, Procedure SalesMTDSubQuery, Line 7
    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>Since I'm new in SQL and still learning, but I don't understand how to solve this. Any help will be appreciated. Thank you.</p>