title: Is there a way to avoid columns from GROUP BY
body: <p>My table has columns such as ID,Perdium and Location so I want to calculate all the perdiums given to an employee and the perdium share given in NY. The issue which I am facing is that SQL Server engine is throwing as error stating that location column isnt present in the GROUP BY clause(as needed in my use-case).If I include the location in the Group By clause I always get NYPerdiumShare as 1 which is not what I am expecting. Is there any workaround to this?</p>
  
  <pre><code>WITH CTE_Employee AS
      (
          SELECT  ID,
                  SUM(Perdium) AS TotalPerdium,
                  CASE WHEN Location='NY' THEN SUM(Perdium) ELSE NULL END AS NYPerdium FROM EmployeePerdium
          GROUP BY ID
      )
  
  SELECT  ID,
          TotalPerdium,
          NYPerdium/TotalPerdium AS NYPerdiumShare 
      FROM CTE_Employee
  </code></pre>