generate an sql query where we have to check whether the patients are double positive or triple postive based on the following condition:

If MYC positive, BCL$2$ positive then double positive

If MYC positive, BCL$6$ positive then double positive

If MYC positive, BCL$2$ positive and BCL$6$ positive, then triple positive.

There are three columns to check it MYC$,$ BCL$2,$BCL$6$ which have corresponding values metioned in the above condition in their respective columns.

I have created a query as follows :

SELECT $*,$

CASE

WHEN "COUNT$\_$DISTINCT$($MYC$)"=\text{'}$MYC$\text{'}$ AND "COUNT$\_$DISTINCT$($BCL$2)"=\text{'}$BCL$2\text{'}$ THEN 'Double Hit'

WHEN "COUNT$\_$DISTINCT$($MYC$)"=\text{'}$MYC$\text{'}$ AND "COUNT$\_$DISTINCT$($BCL$6)"=\text{'}$BCL$6\text{'}$ THEN 'Double Hit'

WHEN "COUNT$\_$DISTINCT$($MYC$)"=\text{'}$MYC$\text{'}$ AND "COUNT$\_$DISTINCT$($BCL$2)"=\text{'}$BCL$2\text{'}$ AND "COUNT$\_$DISTINCT$($BCL$6)"=\text{'}$BCL$6\text{'}$ THEN 'Triple Hit'

ELSE 'None'

END AS Hit$\_$Status

FROM #table# AS "table";

but the output is returning the value double positive even if all the three columns contain the corresponding columns.

eg$.$ MYC is postive, BCL$2$ is positive, BCL$6$ is positive still returns double positive instead of triple positive.