I'd like to set up a view field grouping on a condition. Is that possible?
In particular, I have an Ubercart site, and I'd like to make a report that groups in-state orders together and out-of-state orders together (so their totals can be summed). I just want two groups. I don't want a separate group for every single state.
I'm no expert on SQL, but it would be vaguely like this:
SELECT CASE WHEN uc_order.billing_zone = 12 THEN "In-state" ELSE "Out-of-state" END AS in_state, ....
...
GROUP BY in_stateI could simply set up a filter on this condition and make two views, but that gets messy, as I have two conditions, which leads to four separate views.... I'd rather have a single view with four lines.
It seems pretty natural to have a boolean field like this that uses the filter conditions to select output, but I don't see any way to do that.
This could be done with a boolean Computed Field attached to orders and saved in the DB, but that's kind of ugly.
It could also be done directly with http://drupal.org/project/views_raw_sql, but that's also ugly, and error-prone, as well.
Anyone know a better way?