SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    UK
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieving Quarterly Sales Totals

    Table Format:

    id mediumint(8) auto_increment
    product mediumint(8)
    customer_id mediumint(8)
    timestamp int(10)
    status tinyint(1)
    total decimal(10,2)

    We have four payment periods:

    First Day of Decemember through to Last Day of February
    First Day of March through to Last Day of May
    First Day of June through to Last Day of August
    First Day of September through to Last Day of November

    What I want to do is retrieve the sum of {total} for each quarterly period so that I have four quarterly payment amounts. Using PHP, by the way..

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select sum(case when month(from_unixtime(`timestamp`))
                      in ( 12, 1, 2 )
                    then total else 0 end) as q1
         , sum(case when month(from_unixtime(`timestamp`))
                           in ( 3, 4, 5 )
                    then total else 0 end) as q2
         , sum(case when month(from_unixtime(`timestamp`))
                           in ( 6, 7, 8 )
                    then total else 0 end) as q3
         , sum(case when month(from_unixtime(`timestamp`))
                           in ( 9, 10, 11 )
                    then total else 0 end) as q4
      from sales
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    UK
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow. Absolutely stunning response. Thank you so much. Worked straight "out of the box".


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
  •