SQL Row value count

Transact-SQL
1

Dear All,

I have a table with the following rows and column

staffID Date day1 day2 day3 day4 day5 day6 day7 day8
123 17/07/2017 P P MA MA P P P P
225 17/07/2017 P P P P P P P P
101 17/07/2017 MA CL CL CL P P P P

I want to count in each row the number of P, MA, CL,

Please i need help on this.

2

Do you mean:

select sum(case when day1='P' then 1 else 0 end) as day1
      ,sum(case when day2='P' then 1 else 0 end) as day2
      ,sum(case when day3='P' then 1 else 0 end) as day3
      ,sum(case when day4='P' then 1 else 0 end) as day4
      ,sum(case when day5='P' then 1 else 0 end) as day5
      ,sum(case when day6='P' then 1 else 0 end) as day6
      ,sum(case when day7='P' then 1 else 0 end) as day7
      ,sum(case when day8='P' then 1 else 0 end) as day8
  from yourtable
;
3

Thank you so much for you reply but i want a result of this format
StaffID P MA CL
123 6 2 0
225 8 0 0
101 4 1 3

please i do i get this result

4 
select sum(case when day1='P' then 1 else 0 end)
      +sum(case when day2='P' then 1 else 0 end)
      +sum(case when day3='P' then 1 else 0 end)
      +sum(case when day4='P' then 1 else 0 end)
      +sum(case when day5='P' then 1 else 0 end)
      +sum(case when day6='P' then 1 else 0 end)
      +sum(case when day7='P' then 1 else 0 end)
      +sum(case when day8='P' then 1 else 0 end) as p
      ,sum(case when day1='MA' then 1 else 0 end)
      +sum(case when day2='MA' then 1 else 0 end)
      +sum(case when day3='MA' then 1 else 0 end)
      +sum(case when day4='MA' then 1 else 0 end)
      +sum(case when day5='MA' then 1 else 0 end)
      +sum(case when day6='MA' then 1 else 0 end)
      +sum(case when day7='MA' then 1 else 0 end)
      +sum(case when day8='MA' then 1 else 0 end) as ma
      ,sum(case when day1='CL' then 1 else 0 end)
      +sum(case when day2='CL' then 1 else 0 end)
      +sum(case when day3='CL' then 1 else 0 end)
      +sum(case when day4='CL' then 1 else 0 end)
      +sum(case when day5='CL' then 1 else 0 end)
      +sum(case when day6='CL' then 1 else 0 end)
      +sum(case when day7='CL' then 1 else 0 end)
      +sum(case when day8='CL' then 1 else 0 end) as cl
  from yourtable
;
5

How about something like this?

Declare @testTable Table (StaffID int, EntryDate date, Day1 char(2), Day2 char(2), Day3 char(2), Day4 char(2), Day5 char(2), Day6 char(2), Day7 char(2), Day8 char(2));

Insert Into @testTable (StaffID, EntryDate, Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8)
Values (123, '2017-07-17',  'P',  'P', 'MA', 'MA', 'P', 'P', 'P', 'P')
     , (225, '2017-07-17',  'P',  'P',  'P',  'P', 'P', 'P', 'P', 'P')
     , (101, '2017-07-17', 'MA', 'CL', 'CL', 'CL', 'P', 'P', 'P', 'P');

Select *
     , pCount = (Select count(t.dayValues)
                   From (
                 Values (Day1), (Day2), (Day3), (Day4), (Day5), (Day6), (Day7), (Day8)
                        ) As t (dayValues)
                  Where t.dayValues = 'P')                          
     , maCount = (Select count(t.dayValues)
                    From (
                  Values (Day1), (Day2), (Day3), (Day4), (Day5), (Day6), (Day7), (Day8)
                         ) As t (dayValues)
                   Where t.dayValues = 'MA')                          
     , clCount = (Select count(t.dayValues)
                    From (
                  Values (Day1), (Day2), (Day3), (Day4), (Day5), (Day6), (Day7), (Day8)
                         ) As t (dayValues)
                   Where t.dayValues = 'CL')                          
  From @testTable;
6

Here is a better version:

Declare @testTable Table (StaffID int, EntryDate date, Day1 char(2), Day2 char(2), Day3 char(2), Day4 char(2), Day5 char(2), Day6 char(2), Day7 char(2), Day8 char(2));

Insert Into @testTable (StaffID, EntryDate, Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8)
Values (123, '2017-07-17',  'P',  'P', 'MA', 'MA', 'P', 'P', 'P', 'P')
     , (225, '2017-07-17',  'P',  'P',  'P',  'P', 'P', 'P', 'P', 'P')
     , (101, '2017-07-17', 'MA', 'CL', 'CL', 'CL', 'P', 'P', 'P', 'P');

Select *
  From @testTable
 Cross Apply (Select sum(iif(t.dayValues = 'P', 1, 0)) As pCount
                   , sum(iif(t.dayValues = 'MA', 1, 0)) As maCount
                   , sum(iif(t.dayValues = 'CL', 1, 0)) As clCount
                From (
              Values (Day1), (Day2), (Day3), (Day4), (Day5), (Day6), (Day7), (Day8)
                     ) As t (dayValues)) As c
1 Like