A column aggregation function that determines if all of the values are equal to a constant in Sybase IQ

Question

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! ;-)

Answer 1

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