时间戳

首页 > 解决方案 > 如何在部分百分比的 SELECT 语句中避免 SELECT COUNT

sql-server - 如何在部分百分比的 SELECT 语句中避免 SELECT COUNT

问题描述

我想在这里计算每个 SiteName 的部分百分比，但我需要计算我的行的总计数。

以下查询有效，但有没有办法在不使用 SELECT 语句中的 SELECT 或为此声明变量的情况下执行此操作？我只有读取权限，所以我不能声明变量。

SELECT

ServiceSiteName   
, COUNT(*)                                  AS [Alarms Resolved]
-- How can I avoid this.
, (SELECT COUNT(1) FROM [C1Datastore].[dbo].[Fct_AlertCRM_Incident] 
    WHERE Conditions A, B, C)       AS [Total Count]
    
, COUNT(*) / (SELECT COUNT(1) FROM [C1Datastore].[dbo].[Fct_AlertCRM_Incident] 
    WHERE Conditions A, B, C)       AS [% Count]

FROM TableX
WHERE Conditions A, B, C

GROUP BY 
ServiceSiteName

ORDER BY [Alarms Resolved]  DESC

标签： sql-serveraggregate

解决方案

关键是使用 SUM(COUNT()) OVER (PARTITION BY) 这要短得多。

SELECT

ServiceSiteName   
, COUNT(*)                                  AS [Alarms Resolved]

    
-- Use this 
, SUM(COUNT(1)) OVER (PARTITION BY ServiceSiteName)     AS [TOTAL]
, COUNT(*) / SUM(COUNT(1)) OVER (PARTITION BY ServiceSiteName)         AS [% Count]


FROM TableX
WHERE Conditions A, B, C

GROUP BY 
ServiceSiteName

ORDER BY [Alarms Resolved]  DESC

推荐阅读