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

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.

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

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:

<pre>
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
</pre>