Count of values grouped by a row id

My data looks like the below,

I want to sum up the count of occurrences of ALL. This should be grouped by Id. So the output should look like,

ID Count
1    5
2    4

My query looks like,

select id,
       (count(Monday) + count(Tuesday) + count(Wednesday) + count(Thursday) +
       count(Friday) + count(Saturday) + count(Sunday)) as 'Count'
  from Report
 where Monday = 'ALL'
    or Tuesday = 'ALL'
    or Wednesday = 'ALL'
    or Thursday = 'ALL'
    or Friday = 'ALL'
    or Saturday = 'ALL'
    or Sunday = 'ALL'
 group by Id;

This query is giving me the result as,

ID Count
 1    5
 2    6

which is incorrect for Id 2. The value for Friday for the second row is an empty string.

dbfiddle

Answer

You can use counting for all days of a week with case being equal to ALL

SELECT Id, COUNT(Monday='ALL') + COUNT(Tuesday='ALL') + COUNT(Wednesday='ALL')+ 
           COUNT(Thursday='ALL') + COUNT(Friday='ALL') + COUNT(Saturday='ALL')+ 
           COUNT(Sunday='ALL')
  FROM `Report` 
 GROUP BY Id ;

Demo