I’ve got a table with columns: foo, bar - where foo is some potentially repeated id and bar is an int between 0 and 5.
I’d like to get a summary table with: foo, count(where bar == 0) - count(where bar == 1)
I can’t seem to find a way to do this in a nice way. I could use some generated column with value either 1 or -1 depending on bar, then use a Sum of that instead. But it feels like I’m missing a simpler solution.
I could use some generated column with value either 1 or -1 depending on bar, then use a Sum of that instead.
That’s actually a really great idea.
To do it more ‘directly’ without adding an extra column to your source table, try this formula in the summary table:
len([r for r in $group if r.bar == 0]) - len([r for r in $group if r.bar == 1])
or
sum((r.bar == 0) - (r.bar == 1) for r in $group)
(The second formula uses the fact that True==1 and False==0 in Python)