SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get Total Sum Spent Every Month

    Lets say i got the following table structure:
    The date column is MYSQL date column.
    ID | Item | Price | Date
    1 | Test1 | 4 | 2002-05-01,00:00:00
    2 | Test2 | 6 | 2002-05-02 00:00:00
    3 | Test3 | 8 | 2003-05-03 00:00:00
    4 | Test4 | 3 | 2003-05-04 00:00:00
    How do I get the total amount of money spent for a month in the format of maybe :
    May 2002 - 10
    May 2003 - 11
    Is there any mysql SELECT statment that can do the trick?

    I tried
    Code:
    SELECT YEAR( date ) ,  MONTH ( date ), SUM( price )  FROM gamerz_items GROUP  BY YEAR( date )  AND  MONTH ( date ) ORDER  BY date DESC
    Couldn't work.

    Thanks in advance.

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since your "date" field is a string you could do it this way:

    SELECT SUM(price) WHERE Date LIKE '%-monthNumber-%';

    I've not used SUM() before, so the syntax may be incorrect.
    John

  3. #3
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Since your "date" field is a string
    Looks like a datetime to me.

    This sounds like one for Rudy. Can an advisor move to MySQL so he sees it?
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  4. #4
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Since your "date" field is a string you could do it this way:

    SELECT SUM(price) WHERE Date LIKE '%-monthNumber-%';

    I've not used SUM() before, so the syntax may be incorrect.
    Ops sorry, is a datetime

  5. #5
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by anode
    Looks like a datetime to me.

    This sounds like one for Rudy. Can an advisor move to MySQL so he sees it?
    sorry for posting in the wrong forum

  6. #6
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by GamerZ
    Ops sorry, is a datetime
    It doesn't matter, I had assumed your "Date" was the name of your field, so substitute your fieldname in place of "Date".
    John

  7. #7
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP John, you may be mis-undertsanding that it's a field of type "datetime", not type "string."
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  8. #8
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I meant by "string" is that the info is NOT an integer.
    John

  9. #9
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm so no other way?

  10. #10
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you even try it?

    SELECT SUM(price) WHERE [yourFieldName] LIKE '%-monthNumber-%';
    John

  11. #11
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Did you even try it?

    SELECT SUM(price) WHERE [yourFieldName] LIKE '%-monthNumber-%';
    yap, it can work
    but lets say i need to get all the 12 months, i need to query it 12 times?

    this is the code i try
    PHP Code:
    SELECT SUMprice )  FROM gamerz_items WHERE date LIKE  '%-05-%' 

  12. #12
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, you would have to set the begining date and the ending date for the query, and you would not use the LIKE.

    I have to say that I have found that using a timestamp instead of a datetime data type makes date sensitive queries easier.
    John

  13. #13
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT YEAR(date) AS yr, MONTH (date) AS mth, SUM( price )
    FROM gamerz_items
    GROUP BY yr, mth
    ORDER BY yr DESC, mth DESC

  14. #14
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by redemption
    Code:
    SELECT YEAR(date) AS yr, MONTH (date) AS mth, SUM( price )
    FROM gamerz_items
    GROUP BY yr, mth
    ORDER BY yr DESC, mth DESC
    Thanks dude, that does the trick

  15. #15
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    No, you would have to set the begining date and the ending date for the query, and you would not use the LIKE.

    I have to say that I have found that using a timestamp instead of a datetime data type makes date sensitive queries easier.
    Thanks John, the code from redemption just doe the trick


Bookmarks

Posting Permissions

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