On 25/07/2009 6:17 AM, David Bicking wrote: > That works. Thanks! It struck me that Pavel's revised query didn't mention the d2 column at all, only d1:
>> sum(case when d1='X' then 1 else -1 end) as act_sum, >> sum(case when d1='X' then amt else -amt end) as net ... backtracking, it seems that you haven't stated anything about the possibility of having 'X' in neither d1 nor d2. You may like to program this along more defensive lines: sqlite> create table source (id integer, d1 text, d2 text, amt int); sqlite> insert into source values(1, 'X', 'Y', 15); sqlite> insert into source values(2, 'X', 'Z', 6); sqlite> insert into source values(2, 'A', 'X', 7); sqlite> insert into source values(3, 'B', 'X', 12); sqlite> insert into source values(4, 'C', 'W', 99); sqlite> .headers on sqlite> select id, ...> case kind ...> when 1 then 'NEW' ...> when 2 then 'DROP' ...> when 3 then 'CHANGE' ...> else 'WHOOPS' ...> end as Action, ...> net ...> from ( ...> select id, ...> sum((d1='X') + (d2='X') * 2) as kind, ...> sum(((d1 = 'X') - (d2 = 'X')) * amt) as net ...> from source ...> group by id ...> ) ...> order by 2 desc ...> ; id|Action|net 4|WHOOPS|0 1|NEW|15 3|DROP|-12 2|CHANGE|-1 sqlite> select id, ...> case when act_sum = 1 then 'NEW' ...> when act_sum = 0 then 'CHANGE' ...> else 'DROP' ...> end as Action, ...> net ...> from ...> ( ...> select id, ...> sum(case when d1='X' then 1 else -1 end) as act_sum, ...> sum(case when d1='X' then amt else -amt end) as net ...> from Source ...> group by id ...> ) ...> order by 2 desc; id|Action|net 1|NEW|15 3|DROP|-12 4|DROP|-99 2|CHANGE|-1 sqlite> An accounting system where the sign of the amount is detached and has to be obtained from another column is tedious and error-prone; obtaining it from TWO columns is "interesting"; a system where the nature of the transaction or event is not recorded and has to be reverse-engineered from circumstantial evidence is also "interesting" (especially where the evidence spans more than one row) -- is this an existing system, or is it a prototype that you could reconsider? HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users