SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting totals over different time periods in one query?

    Using MySQL 3.23.38.

    With a table of phone calls like:

    CALLS:
    ID
    incoming_from
    time_of_call
    date_of_call

    I want to calculate a report based on this table with the following:

    April total # calls
    May total # calls
    .....
    December total # calls

    Can I get all those totals in one query?

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I haven't tested it, but try the following:

    Code:
    SELECT DATE_FORMAT(date_of_call, '%M') as month_name
         , COUNT(id) as incoming_calls
    FROM calls GROUP BY month_name
    ORDER BY MONTH(date_of_call) ASC
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    lilleman, after the GROUP BY has done its grouping, MONTH(date_of_call) is not available in the working set of rows

    either SELECT and GROUP BY the month number as well, or do something like this --
    Code:
    order by
      position( lower(substring(month_name from 1 for 3))
             in 'janfebmaraprmayjunjulaugsepoctnovdec' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks interesting Rudy. I tried a test on this idea, I get an error message of
    Unknown column month_name in order clause.

    In case i've messed something up here is my whole query:

    Code:
     SELECT DATE_FORMAT(mydatetime, '%M') as month_name,
     COUNT(id) AS calls
     FROM timetest
     GROUP BY
     month_name
     ORDER BY
       POSITION( LOWER(SUBSTRING(month_name from 1 for 3))
     		 in 'janfebmaraprmayjunjulaugsepoctnovdec' )
    Is it something I've messed up in combining your order by and Erik's example?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    probably just another stupid mysql alias problem

    note month_name is an alias and it's okay to use it in the GROUP BY

    it's also okay to use it in the ORDER BY, e.g. ORDER BY month_name desc

    but apparently not inside a function in the ORDER BY

    so, where you have month_name in the substring function, replace that with the DATE_FORMAT expression

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that does it, thanks as always.

    Code:
       SELECT DATE_FORMAT(mydatetime, '%M') as month_name,
       COUNT(id) AS calls
       FROM timetest
       GROUP BY
       month_name
       ORDER BY
         POSITION( LOWER(SUBSTRING(DATE_FORMAT(mydatetime, '%M') 
     	 from 1 for 3))
       		 in 'janfebmaraprmayjunjulaugsepoctnovdec' )

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it working guys - many thanks!

  8. #8
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    lilleman, after the GROUP BY has done its grouping, MONTH(date_of_call) is not available in the working set of rows
    I didn't know that (obviously). Thanks for pointing it out.
    ERIK RIKLUND :: Yes, I've been gone quite a while.


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
  •