Return rows which have values in multiple columns

I have a table which records the number of times a user logs into a page, but I am trying to filter out where the user has logged in at least twice throughout the week

Here is the table below

User Monday Tuesday Wednesday Thursday Friday Total
A 1 3 4 6 1 15
B 0 0 20 0 0 20
C 18 1 0 18 1 38
D 0 2 0 0 0 2

Here is my expected output

User Monday Tuesday Wednesday Thursday Friday Total
A 1 3 4 6 1 15
C 18 1 0 18 1 38

>Solution :

Use a where clause case expression to evaluate each day for value > 0 and then sum the total >=2. Each day must be evaluated on its own merit so we can not do simple math and divide or something. Leaving us to evaluate each day independently.

SELECT * 
FROM TABLENAME
WHERE case when Monday > 0  then 1 else 0 end 
    + case when Tuesday > 0 then 1 else 0 end  
    + case when Wednesday > 0 then 1 else 0 end 
    + case when Thrusday > 0 then 1 else 0 end 
    + case when Friday > 0 then 1 else 0 end >=2

Leave a Reply