使用GROUP BY时，使用T-SQL语句计算百分比

Question

在本教程的这一部分中，我想添加到示例中，并了解如何使用此查询的变体:

USE AdventureWorks2012;
GO
SELECT 
    DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
    COUNT(WorkOrderID) AS 'Late Orders',
    (COUNT(WorkOrderID) / SUM(COUNT(WorkOrderID)) AS '% of Late Orders'
 FROM Production.WorkOrder
 WHERE DueDate < EndDate
 GROUP BY DATEDIFF(dd,DueDate,EndDate)
 ORDER BY DATEDIFF(dd,DueDate,EndDate);

要制作这个表，其中"延迟订单的%"给出了延迟天数的计数，并将其除以所有延迟的订单，它应该看起来像这样:

----------------------------------------
|Days Late|Late Orders|% of Late Orders|
----------------------------------------
|21       |784        |10              |
|18       |1285       |14              |
----------------------------------------

我已经尝试了这个查询的几个变体，查看了几个相关的StackOverflow问题，但是不能在没有收到错误消息的情况下让这个表工作。这个问题最接近我的答案，但结果是错的。

谢谢你的帮助。

Answer 1

问题是，由于您正在使用GROUP BY语句，所有聚合函数都在子组上操作。您将需要单独计算"所有延迟订单"的值，以便获得所有延迟订单的值，而不是具有给定延迟时间的所有延迟订单的值，可能使用CTE。

也许像这样:

USE AdventureWorks2012;
GO
;WITH late (num_late_orders)
AS
(
    SELECT COUNT(WorkOrderID)
    FROM Production.WorkOrder
    WHERE DueDate < EndDate
)
SELECT 
    DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
    COUNT(WorkOrderID) AS 'Late Orders',
    (CAST(COUNT(WorkOrderID) AS decimal(14, 3)) / CAST(MAX(late.num_late_orders) AS decimal(14, 3))) AS '% of Late Orders'
FROM Production.WorkOrder
    CROSS JOIN late
WHERE DueDate < EndDate
GROUP BY DATEDIFF(dd,DueDate,EndDate)
ORDER BY DATEDIFF(dd,DueDate,EndDate);

你可能会感到困惑，为什么我有MAX()围绕late.num_late_orders。原因是由于该值未包含在GROUP BY子句中，因此必须包含在聚合语句中，即使该值在整个查询中是恒定的。

Answer 2

试试这个:

SELECT  DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
COUNT(WorkOrderID) AS 'Late Orders',
(COUNT(WorkOrderID)*1.0 / (select count(1) as TotalLateOrders FROM Production.WorkOrder
WHERE DueDate < EndDate))
AS '% of Late Orders'
FROM Production.WorkOrder
WHERE DueDate < EndDate
GROUP BY DATEDIFF(dd,DueDate,EndDate)
ORDER BY DATEDIFF(dd,DueDate,EndDate);

我要乘以1.0来除以浮点数，你也可以对上一个答案这么做