For simplicity, let's say each record in my table represents a single day (mm/dd/yyyy) and the amount of money spent on that day (e.g. 95).
I want to sum up the amount of money spent in each month and display it.
My select clause should be something like...
SELECT theDate, SUM(moneySpent) AS monthlySpent FROM table
GROUP BY SUBSTRING(theDate, 0, 2)
The SUBSTRING function is of course trying to parse out the month from the date field formatted as mm/dd/yyyy. However, this does not work because I cannot use GROUP BY with a function. So how can I display the total amount of money spent in each month?