EXCLUDE field from GROUP BY when processing SUM

Transact-SQL
1

Hello SQL expert,

How do I Sum Spending field without including Datespending in my group?
Below is my query:

Select Material
,Spending
,DateSpending
From PurchasingTbl

these are some samples from bove query result set is:

Material Spending DateSpending
A $50.45 01/01/2016
B $27.33 01/02/2016
D $34.56 01/05/2016
F $66.66 01/13/2016
G $83.46 01/28/2016
A $88.11 02/05/2016
C $33.26 02/17/2016
G $99.17 02/25/2016
B $13.10 03/10/2016
C $76.88 03/15/2016
G $87.12 03/26/2016
A $55.33 04/17/2016
H $98.12 04/25/2016

The purpose I dont want to include DateSpending in the grouping by
when I do sum of spending so I can total sum when I custom my
DateSpending parameter by monhtly.

2

So what should the output of this data be?

3

Select Material
,SUM(Spending) AS Spending
,DATEADD(MONTH, DATEDIFF(MONTH, 0, DateSpending), 0) AS DateMonth
From PurchasingTbl
GROUP BY Material, DATEADD(MONTH, DATEDIFF(MONTH, 0, DateSpending), 0)
ORDER BY DateMonth, Material

4

The easiest way to handle that is to use an aggregate function on the field you do not want to group on. Examples would be MAX(), MIN(), AVG() TOP 1, etc.

If I misunderstood the question, it could be that you want to do a subquery to get the fields and the outer query using the sum from the subquery to leave the DateSpending behind.