If I understand the question correctly you have a table containing a separate column for each weekday?
If this is the case, I would suggest different kind of structure. Instead of using separate columns, use rows. In other words the table could look something like
DutyTable
---------
- EmpName
- Weekday
- DutyType
And data could be something like (1 = sunday, 7 = saturday)
EmpName Weekday DutyType
------- ------- --------
Mr Smith 1 A
Mr Smith 2 M
Mr Smith 3 M
Mr Smith 4 M
Mr Smith 5 M
Mr Smith 6 M
Mr Smith 7 B
Now your query could look something like
SELECT EmpName,
SUM(CASE DutyType
WHEN 'M' THEN 1
ELSE 0
END)
FROM DutyTable
GROUP BY EmpName
This is just a simple example so you probably need to add proper columns to match your requirements.
ADDITION:
---------
Consider the following example
CREATE TABLE DutyTest (
DutyId int,
EmpName varchar(100),
DayId int,
DutyType char
);
INSERT INTO DutyTest VALUES
(1, 'Mr Smith', 1, 'M'),
(2, 'Mr Smith', 2, 'M'),
(3, 'Mr Smith', 3, 'M'),
(4, 'Mr Smith', 4, 'M'),
(5, 'Mr Smith', 5, 'A'),
(6, 'Mr Smith', 6, 'D'),
(7, 'Mr Smith', 7, 'A'),
(8, 'Mr Wick', 1, 'A'),
(9, 'Mr Wick', 2, 'A'),
(10, 'Mr Wick', 3, 'D'),
(11, 'Mr Wick', 4, 'A'),
(12, 'Mr Wick', 5, 'M'),
(13, 'Mr Wick', 6, 'A'),
(14, 'Mr Wick', 7, 'A');
SELECT EmpName,
SUM(CASE DutyType
WHEN 'M' THEN 1
ELSE 0
END) AS M_Days
FROM DutyTest
GROUP BY EmpName;
The result for the query would be
EmpName M_Days
-------- ------
Mr Smith 4
Mr Wick 1
ADDITION 2
-----------
To query the current table in the database you could use something like the following
CREATE TABLE DutyTest2 (
DutyId int,
EmpName varchar(100),
Sunday char,
Monday char,
Tuesday char,
Wednesday char,
Thursday char,
Friday char,
Saturday char
);
INSERT INTO DutyTest2 VALUES
(1, 'Mr Smith', 'M', 'M', 'M', 'M', 'A', 'D', 'A'),
(2, 'Mr Smith', 'M', 'M', 'D', 'A', 'A', 'D', 'A'),
(3, 'Mr Wick', 'A', 'A', 'A', 'M', 'A', 'D', 'A'),
(4, 'Mr Wick', 'M', 'M', 'D', 'A', 'M', 'M', 'A');
SELECT DutyId,
EmpName,
SUM(CASE Sunday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Monday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Tuesday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Wednesday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Thursday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Friday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Saturday WHEN 'M' THEN 1 ELSE 0 END) AS M_Days
FROM DutyTest2
GROUP BY DutyId,
EmpName;
The query should produce
DutyId EmpName M_Days
------ -------- ------
1 Mr Smith 4
2 Mr Smith 2
3 Mr Wick 1
4 Mr Wick 4
Hope this helps