 # SQL Row value count

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.

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
;
``````

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

``````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
;
``````

``````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;
``````

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