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.
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