如何计算P&在sqlserver中 [英] How to count P & a in sqlserver

问题描述

请帮助别人！

我有一张名为Mark_Attendance的表格，其中包含

Please help someone!
I have a table in name of Mark_Attendance which contains columns like

[Class] | [S_Adm_No] |  [Alias], | [DateTime] 

要获得我的结果，我使用的是SQL查询，例如

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 | 

我的问题是我如何分别计算P和A.

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'

推荐答案

尝试加入表格with

Try to join a table with

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]

详情请见：< a href =https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins> SQL连接的可视化表示 [ ^ ]

这篇关于如何计算P&amp;在sqlserver中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！