Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Group By [Function]

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

  4. #4
    Join Date
    Jul 2009
    Posts
    150
    I am not sure, but can he use SUBSTR directly to DATE type ?

    Maybe he has to use CHAR function inside of SUBSTR ?

    ....
    SUBSTR(CHAR(theDate), 1, 2)
    ....

    Also, he used position 0, which is restricted for string.

    And I am agree with you, tonkuma. MONTH and YEAR is better then SUBSTR.

    Kara S.
    Last edited by DB2Plus; 09-25-09 at 00:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •