nityanand NCR Ответов: 1

Как подсчитать P & a в sqlserver

Пожалуйста, помогите кому-нибудь!
У меня есть таблица с именем Mark_Attendance, которая содержит такие столбцы, как 
[Class] | [S_Adm_No] |  [Alias], | [DateTime]


Чтобы получить свой результат, я использую sql-запрос типа 

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'


Этот запрос показывает значение типа

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 |


Мой вопрос в том, как я могу считать P и A отдельно.

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


Что я уже пробовал:

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'

1 Ответов

Рейтинг:
4

Maciej Los

Попробуйте присоединиться к столу с 

SELECT pt.[Class], pt.[S_Adm_No], pt.[1], pt.[2], ..., pt.[31], at.[SumOfP], at.[SumOfA]
    FROM (
        SELECT [Class], [S_Adm_No], [Alias],DATENAME(M, [DateTime])as [Month], DAY([DateTime]) as [DayValue] 
        FROM [View_SMS_Mark_Attendance]) as Composite
        WHERE [Month] = 'May' and Class ='Nur-A'
        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 pt LEFT JOIN (
       SELECT [Class], [S_Adm_No], SUM(CASE WHEN [Alias] = 'P' THEN 1 ELSE 0 END) AS SumOfP, SUM(CASE WHEN [Alias] = 'A' THEN 1 ELSE 0 END) AS SumOfA
       FROM  [View_SMS_Mark_Attendance] 
       GROUP BY [Class], [S_Adm_No]
           ) AS at ON pt.[Class] = at.[Class] AND pt.[S_Adm_No] = at.[S_Adm_No]


Для получения более подробной информации, пожалуйста, смотрите: Визуальное представление SQL-соединений[^]

nityanand NCR

5

Maciej Los

Спасибо.