Oops, sorry! I misunderstood what you need. Try this: 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;
Pavel On Fri, Jul 24, 2009 at 3:44 PM, David Bicking<dbic...@yahoo.com> wrote: > > >> From: Pavel Ivanov <paiva...@gmail.com> >> >> Just do the same approach with CASE you've already used: >> >> Select ID, >> CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' >> WHEN d1 IS NULL AND d2='X' THEN 'DROP' >> ELSE 'CHANGE' END AS Action, >> Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN >> d2='X' THEN >> AMT ELSE 0 END) AS Net >> FROM Source GROUP BY ID ORDER BY 2 DESC; >> >> Pavel >> > > Thanks, unfortunately, the ELSE 'CHANGE' never fires as all records match > either the first or second WHEN clauses. > > Using your query, I got > 1 | NEW | 15 > 2 | DROP | -1 > 3 | DROP | -12 > > And it occurs to me that I misstated the problem slightly. The source is > actually; > > 1 | X | Y | 15 > 2 | X | Z | 6 > 2 | A | X | 7 > 3 | B | X | 12 > > Where A,B,Y and Z are arbitrary values that aren't = 'X'. Probably Y and Z > would be greater than X, and A and B would be less than X, but it isn't > guaranteed. > > David > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
