quique123 Posted August 15, 2013 Share Posted August 15, 2013 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] Link to comment Share on other sites More sharing options...
djohnson53 Posted August 15, 2013 Share Posted August 15, 2013 This part:MONTH(account_invoice.create_date)MONTH[/code] Doesn't look legit. Link to comment Share on other sites More sharing options...
quique123 Posted August 16, 2013 Author Share Posted August 16, 2013 yeah, like i said, neither works. What Im trying to find out is what would work? Link to comment Share on other sites More sharing options...
Solution ajinkya_c Posted August 16, 2013 Solution Share Posted August 16, 2013 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.ThanksAjinkya Link to comment Share on other sites More sharing options...
quique123 Posted August 16, 2013 Author Share Posted August 16, 2013 Its openERP db, Postgre SQL Link to comment Share on other sites More sharing options...
quique123 Posted August 16, 2013 Author Share Posted August 16, 2013 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 Link to comment Share on other sites More sharing options...
djohnson53 Posted August 19, 2013 Share Posted August 19, 2013 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now