...

View Full Version : query Question



Atrhick
11-18-2011, 07:24 PM
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.



$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";

Old Pedant
11-18-2011, 08:04 PM
If you want it for 12 months, then don't use

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?

Ahlahn
11-18-2011, 10:22 PM
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.

Old Pedant
11-19-2011, 12:11 AM
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.

Atrhick
11-21-2011, 03:10 PM
Thank you guys so much!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum