> 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
