Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: query Question

  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query Question

    hey guys i have a question about MYSQL, i ma trying to get the average monthly total price for the pass 12 months but i cant seem to find out how. below if my query.

    PHP Code:
     $query_str "SELECT SUM( CONVERT(price_CIQFY_paid, DECIMAL) )  AS MAverage  "
              
    " FROM lead_partners_pages " 
              
    " WHERE date_month = MONTH(NOW()) "
              
    " GROUP BY date_month " 
              
    " ORDER BY date_month"

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    If you want it for 12 months, then don't use
    Code:
       WHERE date_month = MONTH(NOW())
    because that is saying "only get the results for the current month".

    But you don't show enough of your table to tell how to get the past 12 months.

    I see the date_month field, but how do you know which *YEAR* it is? Do you also have a date_year field?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Sep 2010
    Location
    The Twilight Zone
    Posts
    86
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Old pendant is correct- by setting your WHERE clause to the current month, all the other months will drop out.

    It would help if you provide us more info about the schema (tables and fields), but here's what I recommend. I don't know what version mysql you have, but 5.5 offers date functions that allow you to specify intervals. Do some research into it.

    Here's a generic query for you to get started:

    1. Find the average total price for each month in the last 12 months.

    SELECT month_field, avg(price) AS monthly_avg
    FROM month_table
    WHERE DATE_SUB(CURDATE(),INTERVAL 12 MONTH) <= date_column;
    GROUP BY month(month_field);

    2. Now, find the average of the total price field of the table returned by query 1.
    Last edited by Ahlahn; 11-18-2011 at 09:25 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Probably this is not what he wants:

    WHERE DATE_SUB(CURDATE(),INTERVAL 12 MONTH) <= date_column;

    because that will indeed go back exactly 12 months from today.

    Surely he wants 12 months starting on the first day of some month.

    But, yes, we need more info before we can make any attempt at an answer.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you guys so much!


  •  

    Posting Permissions

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