Here's the scenario: I'm building a new criteria set for ticketing information, and one of the things I want to add is Production Element Keywords. Now, this creates lots of duplicate rows, as multiple keywords can be attached to a single performance (not to mention packages! Almost forgot to do those!) Still as a List Critieria that's mostly no problem as I'm trying to pull customer_nos at the end of the day. But! There are a few fields where I want to be able to search on an aggregate value. One is # of Unique Perfs: that one is easy, COUNT(distinct perf_no) is going to work no matter how many times the SLI row is inflated. But I'd also love to have a "Paid Amount" criteria as well. I'd need something like the SUM of the MAX of each (sli_no, keyword_id) grouping? Does anyone know a clever way to get this, or will SUM and sli_no duplication always be incompatible?
Following, I'm curious what you come up with for this. It sounds like something where creating a View might be best?