Group Sales by Month Query

[quique123]

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)[/code]

[djohnson53]

This part:

MONTH(account_invoice.create_date)MONTH[/code]

 

Doesn't look legit.

[quique123]

yeah, like i said, neither works.  What Im trying to find out is what would work?

[ajinkya_c]

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

[quique123]

Its openERP db, Postgre SQL

[quique123]

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

[djohnson53]

The select takes a comma separated list FROM a table. Your list is bad. MONTH() makes sense as a function but MONTH()MONTH is nonsense. This is a syntax error. It may not tbe your only problem, but it will never parse right and will never execute.