group by in queries that contains subqueries

Question

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

Answer 1

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.

Answer 2

Remove the From Customers in the Selects and Move the Where to the root of the query

Change your query to

SELECT
[CustomerID] as 'CustomerID'
, SUM([Purchase]) as 'Purchase'
, SUM([Point]) as 'Point'
FROM [CUSTOMERS]
WHERE [type] in (17,21)
GROUP BY [Customers].[CustomerID]

This will give you

CustomerID  Purchase    Point
1           246         3
2           23434       7

Answer 3

If you're trying to get the total Purchase and total Points per customer, then you need to include the CustomerId in the Purchase sub-query as well as in your main query.

A key thing to remember regarding Group By is that all non-aggregate columns have to be included in it. Therefore in your case, you'd have to group by the Purchase as well.

Your final query would look like:

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