Count of rows matching string in Sybase

Question

I have a table that contains three columns, the first is member_id, the second is an index of months and the third is a categorical variable with three distinct values.

My data looks as follows:

member_id   refill_month    MEMBER_TYPE
454723            0          RETAINED
454723            1          RETAINED
454723            2          RETAINED
454723            4           LAGGER
454723            7          RETAINED
454723            9          RETAINED
454723            11           LOST
456279            0          RETAINED
456279            2            LOST
457100            1          RETAINED
457100            2          RETAINED
457100            3          RETAINED
457100            4          RETAINED
457100            5          RETAINED
457100            6          RETAINED
457100            7          RETAINED
457100            8          RETAINED
457100            9           LAGGER
457100            12           LOST

I would like a count of member_id where the MEMBER_TYPE = 'RETAINED' for each refill_month divided by a count of all member_id for that month

This is what I am trying to get but I don't believe it's possible to include a where clause in the select statement in Sybase.

SELECT 
    refill_month,
    count(member_id where MEMBER_TYPE=’RETAINED’)/count(member_id) AS retention

If anyone can help I would be very grateful.

Answer 1

You can do conditional aggregation. avg() comes handy to compute the ratio of rows that match a given criteria:

select 
    refill_month,
    avg(case when member_type = 'RETAINED' then 1.0 else 0 end) retention
from mytable
group by refill_month
order by refill_month