Group Sales by Month Query
Posted on August 15, 2013 at 10:16am
Im trying to design a SQL query to display all added sales from a partner_id grouped by month.
So I have this so far but im having trouble with the sql syntax. Ive tried month(date), ive tried datepart(mm,date), ive tried date_format(date) and datename(date) but they all return sql error function does not exist in iReport Designer
SELECT SUM(account_invoice.amount_total), MONTH(account_invoice.create_date)MONTH FROM account_invoice WHERE account_invoice.partner_id = 837 GROUP BY MONTH(account_invoice.create_date)
Joined: Feb 19 2013 - 5:51pm
Last seen: 2 years 5 months ago
Posted on August 16, 2013 at 3:13am
Hi,
What is your source i.e.Oracle, Sqlsever, file etc., you should use database specific functions to take month part out of date.
Example: Oracle : to_char('account_invoice.create_date','MONTH')
then group by month.
Thanks
Ajinkya
Joined: Aug 29 2012 - 6:13am
Last seen: 2 years 8 months ago
Posted on August 15, 2013 at 11:46am
This part:
MONTH(account_invoice.create_date)MONTH
Doesn't look legit.
Joined: May 25 2012 - 11:10am
Last seen: 6 hours 47 min ago
Posted on August 15, 2013 at 7:20pm
yeah, like i said, neither works. What Im trying to find out is what would work?
Joined: Feb 19 2013 - 5:51pm
Last seen: 2 years 5 months ago
Its openERP db, Postgre SQL
select case to_char(date_invoice,'mm') when '01' then 'Enero' when '02' then 'Febrero' when '03' then 'Marzo' when '04' then 'Abril' when '05' then 'Mayo' when '06' then 'Junio' when '07' then 'Julio' when '08' then 'Agosto' when '09' then 'Septiembre' when '10' then 'Octubre' when '11' then 'Noviembre' when '12' then 'Diciembre' end from account_invoice