title: Why must I include a where clause column in group by if I am not returning that column as a result?, how can I aggregate duplicate results?
body: <p>I have a query that uses a column in the where clause but does not return that column: </p>
  
  <pre><code>SELECT 
      t1.upc, l1.description, l1.size, 
      l2.Name AS SectionName, l1.section, l3.Name, l1.department,
      (SELECT SUM(QuantitySold) 
       FROM ProductMovement  
       JOIN ProductsMaster ON ProductsMaster.upc = ProductMovement.upc 
       WHERE TimeID &gt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2016') 
         AND TimeID &lt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2017') 
         AND ProductsMaster.upc = l1.upc 
         AND (l1.department = 4 OR l1.department = 13) 
         AND (t1.StoreID = 3 OR t1.StoreID = 9 OR t1.StoreID = 2)) AS Qty,
      (SELECT SUM(TotalSales) 
       FROM ProductMovement  
       JOIN ProductsMaster ON ProductsMaster.upc = ProductMovement.upc 
       WHERE TimeID &gt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2016') 
         AND TimeID &lt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2017') 
         AND ProductsMaster.upc = l1.upc 
         AND (l1.department = 4 OR l1.department = 13) 
         AND (t1.StoreID = 3 OR t1.StoreID = 9 OR t1.StoreID = 2)) AS Sales
  FROM 
      ProductMovement t1
  JOIN 
      ProductsMaster l1 ON l1.upc = t1.upc
  JOIN 
      EnterpriseSections l2 ON l2.SectionID = l1.section
  JOIN 
      EnterpriseDepartments l3 ON l3.DepartmentID = l1.department
  WHERE 
      (SELECT SUM(QuantitySold) 
       FROM ProductMovement  
       JOIN ProductsMaster ON ProductsMaster.upc = ProductMovement.upc 
       WHERE TimeID &gt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2016') 
         AND TimeID &lt;= (SELECT TimeID 
                        FROM Time 
                        WHERE FactDate = '1/14/2017') 
         AND ProductsMaster.upc = l1.upc 
         AND (l1.department = 4 OR l1.department = 13) 
         AND (t1.StoreID = 3 OR t1.StoreID = 9 OR t1.StoreID = 2)) IS NOT NULL
  GROUP BY 
     l1.upc, t1.upc, l1.section, l2.Name, l1.description, l1.size, l1.department, l3.Name
  </code></pre>
  
  <p>When I run this without <code>t1.StoreID</code> in the <code>GROUP BY</code> clause I get the error:</p>
  
  <blockquote>
    <p>Column 'ProductMovement.StoreID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>When I add the column to group by I get duplicate results for every <code>t1.storeID</code> because I am aggregating all of my data anyway.</p>
  
  <p>Why am I required to group by a column I am not returning and how can I aggregate these results?</p>