How Can We Calculate Mysql Table´s Rows Item Group By Month Of More Than 5 Years? · RSS

How Can We Calculate Mysql Table´s Rows Item Group By Month Of More Than 5 Years?

Posted on Sunday, October 4, 2009 · 2 Responses 

I have like more than 5 years record in our database. And I want to calculate them through group by month. Unfortunately, when i try to using sum() function it just shows me the result of one year. but I need to calculate the sum of more than 5 years record group by month .
I am trying to use this following query –
SELECT MONTHNAME( date_create ) AS MONTHSNAME, SUM( quantity ) AS Quantity, SUM( price_unit ) AS Unit, SUM( price_total ) AS Total, SUM( net_price ) AS Price
FROM crm_invoice_details_bs
WHERE 1 =1
AND date_create >= ‘2002-01-01′
AND date_create <= '2007-12-31'
GROUP BY MONTHSNAME ORDER BY MONTH(date_create) ASC
LIMIT 0 , 100
Please help me

Filed under CRM FAQ · Tagged with , , , , , , , , ,

Responses

2 Responses to “How Can We Calculate Mysql Table´s Rows Item Group By Month Of More Than 5 Years?”
  1. Doug G says:
    October 4, 2009 at 11:00 pm

    Are you sure this part is right?
    WHERE 1 =1

  2. Colanth says:
    October 5, 2009 at 4:28 am

    SELECT MONTHNAME( date_create ) AS MONTHSNAME, SUM( quantity ) AS Quantity, SUM( price_unit ) AS Unit, SUM( price_total ) AS Total, SUM( net_price ) AS Price
    FROM crm_invoice_details_bs
    WHERE date_create >= ‘2002-01-01′
    AND date_create <= ‘2007-12-31′
    GROUP BY MONTHSNAME, YEAR ORDER BY YEAR, MONTH(date_create) ASC
    LIMIT 0 , 100
    If you don’t have a year field, substitute a calculation on the date_create field.
    (WHERE 1 = 1? Wazzat?)

Responses