Please help someone!
I have a table in name of Mark_Attendance which contains columns like
[Class] | [S_Adm_No] | [Alias], | [DateTime]
To get my result I am using a sql query like
SELECT * FROM(SELECT [Class] ,[S_Adm_No], [Alias],DATENAME(M, [DateTime])as [Month],
DAY([DateTime]) as [DayValue] FROM [Mark_Attendance]) as Composite PIVOT (MAX([Alias])
FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15],
[16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31]))
AS PivotTable where [Month] = 'May' and Class ='Nur-A'
This query is showing value like
Class | S_Adm_NO | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | and so on
Nur-A | 1101 | April | A | P | P | P | P | A | A |
Nur-A | 1102 | April | P | P | P | P | P | A | A |
Nur-A | 1103 | April | A | P | A | P | P | P | P |
My question is how can I count P and A separately like.
Class | S_Adm_NO | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Total_P | Total_A
Nur-A | 1101 | April | A | P | P | P | P | A | A | 4 | 3
Nur-A | 1102 | April | P | P | P | P | P | A | A | 5 | 2
Nur-A | 1103 | April | A | P | A | P | P | A | A | 3 | 4
What I have tried:
SELECT * FROM(
SELECT [Class] ,
[S_Adm_No],
[Alias],
DATENAME(M, [DateTime])as [Month],
DAY([DateTime]) as [DayValue]
FROM [View_SMS_Mark_Attendance]) as Composite PIVOT (MAX([Alias])
FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7],
[8], [9],[10], [11], [12], [13],
[14],[15],[16], [17], [18], [19],
[20], [21], [22], [23], [24],[25],
[26], [27], [28], [29],[30],[31])
)
AS PivotTable
where [Month] = 'May' and Class ='Nur-A'