I'm trying to write a function to aggregate some columns of text data in a very large Sybase IQ table. I'm not able to change the format of the data provided.
Each row represents the value of a process which is a bit like a test. The key is non-unique and the values are also text-strings which will only be 'pass' or 'fail'.
In the actual table there might be multiple 'Value' columns - but I've only shown one for brevity.
The rule is if everything passed for a key then it's a pass. Otherwise it's a fail. In an ideal world I'd like to be able to write an aggregation function that's a bit like:
count(all(mytable.value == 'pass'))
Here's an example of the data:
| Key | Value | | A | fail | | A | pass | | B | pass | | B | pass | | B | pass | | C | fail | | C | fail |
The aggregated data would look like this:
| Key | Value | | A | fail | | B | pass | | C | fail |
So is there an elegant way to do this?
FYI, Sybase IQ - not regular Sybase! ;-)
This seems like the simplest solution...
select key ,sum(case when value = 'pass' then 1 else 0 end) as num_passed ,count(*) as num_tests from mytable group by key having num_tests = num_passed