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