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
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
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
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
[firebird-support] sum data into single row per day
Hi, I have a question about summing data, as follows: NEW_TABLE CODEAMOUNT SELL_DATE 1 10 10.11.2014 2 20 10.11.2014 3 30 07.11.2014 1 100 11.11.2014 2 200 11.11.2014 2 200011.11.2014 1 150 10.11.2014 3 500 11.11.2014 create or alter procedure new_table_sum ( fromdate date, uptodate date) returns ( sell_date date, stock integer, repair integer, sh integer) as begin 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 do suspend; end 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 I can get the correct format by executing a second SP like this: create or alter procedure new_table_sum2 ( fromdate date, uptodate date) returns ( sell_date date, stock integer, repair integer, sh integer) as begin for select sum(n.stock), sum(n.repair), sum(n.sh), n.sell_date from new_table_sum(:fromdate,:uptodate) n where n.sell_date between :fromdate and :uptodate group by n.sell_date into :stock, :repair, :sh, :sell_date do suspend; end but I want to know if it can be done in a single SP, or is this an acceptable way to get what I want? It applies to many scenarios. Any help gratefully received. Alan -- Alan J Davies Aldis
