Re: [firebird-support] sum data into single row per day
Yes, I see that now and understand the difference between sum(iif and iif(sum. Alan Alan J Davies Aldis On 15/11/2014 11:50, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote: > >Thank you Svein, that's what I tried but kept getting: > >SQL error code = -104. > >Invalid expression in the select list (not contained in either an > aggregate function or the GROUP BY clause). > > > >and yet it now works. I thought it must have been because I was > accessing code in the iif(...). I must have had a typo that > >I could not see for looking. Anyway, that now works perfectly, without > the ',' after SH. > > Sorry about not removing the comma after copying. I think your error > might have been doing IIF(SUM... rather than SUM(IIF... Logically, that > is using an aggregate to decide which single value to return (and hence, > needs to be grouped by), whereas SUM(IIF takes a single value to > determine which value should be used in the aggregation and doesn't need > aggregation. When I try IIF(SUM..., I do get -104 as well. > > Set > > ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] sum data into single row per day
>Thank you Svein, that's what I tried but kept getting: >SQL error code = -104. >Invalid expression in the select list (not contained in either an aggregate >function or the GROUP BY clause). > >and yet it now works. I thought it must have been because I was accessing code >in the iif(...). I must have had a typo that >I could not see for looking. Anyway, that now works perfectly, without the ',' >after SH. Sorry about not removing the comma after copying. I think your error might have been doing IIF(SUM... rather than SUM(IIF... Logically, that is using an aggregate to decide which single value to return (and hence, needs to be grouped by), whereas SUM(IIF takes a single value to determine which value should be used in the aggregation and doesn't need aggregation. When I try IIF(SUM..., I do get -104 as well. Set
Re: [firebird-support] sum data into single row per day
Thank you Svein, that's what I tried but kept getting: SQL error code = -104. Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause). and yet it now works. I thought it must have been because I was accessing code in the iif(...). I must have had a typo that I could not see for looking. Anyway, that now works perfectly, without the ',' after SH. Regards Alan Alan J Davies Aldis On 14/11/2014 08:41, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote: > select sell_date, > sum(iif(code=1, amount,0)) stock, > sum(iif(code=2, amount,0)) repair, > sum(iif(code=3, amount,0)) SH, > from new_table > where n.sell_date between :fromdate and :uptodate > group by sell_date
RE: [firebird-support] sum data into single row per day
> for select > iif(n.code=1,sum(n.amount),0), > iif(n.code=2,sum(n.amount),0), > iif(n.code=3,sum(n.amount),0), > n.sell_date > from new_table n > where n.sell_date between :fromdate and :uptodate > group by n.sell_date,n.code > into > :stock, > :repair, > :sh, > :sell_date > >This gives the correct totals but not in the correct format: > >SELL_DATE STOCK REPAIR SH >07.11.2014 0 0 30 >10.11.2014 160 0 0 >10.11.2014 0 20 0 >11.11.2014 100 0 0 >11.11.2014 0 22000 >11.11.2014 0 0 500 > >What I want is this format, one row per day: > >SELL_DATE STOCK REPAIR SH >07.11.2014 0 0 30 >10.11.2014 160 20 0 >11.11.2014 100 2200500 You almost got it right, Alan, just remember to only group only on things you want to produce a separate row. This is the query you want: select sell_date, sum(iif(code=1, amount,0)) stock, sum(iif(code=2, amount,0)) repair, sum(iif(code=3, amount,0)) SH, from new_table where n.sell_date between :fromdate and :uptodate group by sell_date Set
