Search code examples
sqlsql-servert-sqlgroup-byconditional-aggregation

group by in queries that contains subqueries

How can I group by the following query by customerId

select
(SELECT SUM(Purchase) FROM Customers where type in (17,21)) as 'Purchase'
,(SELECT SUM(Point) FROM Customers) as 'Point'
FROM CUSTOMERS
GROUP BY Customers.CustomerID
Solution

  • Looks like you just want conditional aggregation, which you can do by placing CASE WHEN... inside a SUM.

    SELECT
  c.CustomerId
  SUM(CASE WHEN c.type IN (17, 21) THEN c.Purchase END) AS Purchase,
  SUM(c.Point) AS Point
FROM CUSTOMERS c
GROUP BY
  c.CustomerID;

    Note the use of a table alias to make it more readable, and do not use '' to quote column names, only [] and only where necessary.