Group By [Function]

JimmyTwo · #1 (**permalink**) 09-24-09, 19:42

Hi,

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...

Code:

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?

tonkuma · #2 (**permalink**) 09-24-09, 20:41

What platform and DB2 version are you using?

To extract month from a DATE column, it will be better to use MONTH function.

If GROUP BY expression was used, you can use the expression in SELECT list, like this:

Code:

SELECT MONTH(theDate)  AS month
     , SUM(moneySpent) AS monthlySpent
  FROM table 
 GROUP BY
       MONTH(theDate)
;

tonkuma · #3 (**permalink**) 09-24-09, 20:47

Considering some years of data, you may want to display total amount of money spent in each year, month.

If so, you may want to use query like this:

Code:

SELECT YEAR (theDate)  AS year
     , MONTH(theDate)  AS month
     , SUM(moneySpent) AS monthlySpent
  FROM table 
 GROUP BY
       YEAR (theDate)
     , MONTH(theDate)
;