Group Sales by Month Query

0

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)
quique123's picture
Joined: Feb 19 2013 - 5:51pm
Last seen: 2 years 5 months ago

3 Answers:

1

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

ajinkya_c's picture
935
Joined: Aug 29 2012 - 6:13am
Last seen: 2 years 8 months ago

Its openERP db, Postgre SQL

quique123 - 6 years 3 months ago

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

quique123 - 6 years 3 months ago
0

This part:

MONTH(account_invoice.create_date)MONTH

Doesn't look legit.

djohnson53's picture
76319
Joined: May 25 2012 - 11:10am
Last seen: 6 hours 47 min ago

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.

djohnson53 - 6 years 2 months ago
0

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

quique123's picture
Joined: Feb 19 2013 - 5:51pm
Last seen: 2 years 5 months ago
Feedback
randomness