时间戳

首页 > 解决方案 > 以百分比返回 SQL 结果

sql-server - 以百分比返回 SQL 结果

问题描述

我有以下查询，并且返回数据中的结果集所需的计数为 25%。

尝试运行查询时，我不断收到以下错误：

无法对包含聚合或子查询的表达式执行聚合函数。

SELECT * from
(SELECT 
    l.Title as List
,   CASE 
        WHEN _Custom57 between 13 and 17 THEN '17 and under' 
        WHEN _Custom57 between 18 and 24 THEN '18-24' 
        WHEN _Custom57 between 25 and 34 THEN '25-34' 
        WHEN _Custom57 between 35 and 44 THEN '35-44' 
        WHEN _Custom57 between 45 and 54 THEN '45-54' 
        WHEN _Custom57 >= 55 THEN '55-64' 
        WHEN _Custom57 >= 65 THEN '65+' 
    END as age
    , CAST(Round(count(r.RecipID) * 100.0 / sum(count(*)), 0) as nvarchar(5)) + '%' as RecipCount
FROM MailingRecips mr (NOLOCK)
INNER JOIN Lists l (NOLOCK)
    on mr.ListID = l.ListID
INNER JOIN Recips r (NOLOCK)
    on mr.RecipID = r.RecipID
INNER JOIN RecipsAdditional ra (NOLOCK)
    on r.RecipID = ra.RecipID
WHERE mr.QueueTime >= dbo.UNIX_TIMESTAMP(DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))) and mr.QueueTime < dbo.UNIX_TIMESTAMP(DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
AND mr.ListID IN (
12388,  
12390,  
12412,  
12413,  
12414,  
12429,  
12462,  
12514,
12572 
)
GROUP BY l.Title,   
CASE 
        WHEN _Custom57 between 13 and 17 THEN '17 and under' 
        WHEN _Custom57 between 18 and 24 THEN '18-24' 
        WHEN _Custom57 between 25 and 34 THEN '25-34' 
        WHEN _Custom57 between 35 and 44 THEN '35-44' 
        WHEN _Custom57 between 45 and 54 THEN '45-54' 
        WHEN _Custom57 >= 55 THEN '55-64' 
        WHEN _Custom57 >= 65 THEN '65+' 
    END 
)t
PIVOT (
sum(RecipCount) for age in ([17 and under],[18-24],[25-34],[35-44],[45-54],[55-64],[65+])
) as PVT

标签： sql-serverpercentage

解决方案

我认为有两个问题：

请先使用最大值，否则所有 65+ 也将归入 55-64 类别。

CASE 
        WHEN _Custom57 >= 65 THEN '65+'
        WHEN _Custom57 >= 55 THEN '55-64' 
        WHEN _Custom57 between 13 and 17 THEN '17 and under' 
        WHEN _Custom57 between 18 and 24 THEN '18-24' 
        WHEN _Custom57 between 25 and 34 THEN '25-34' 
        WHEN _Custom57 between 35 and 44 THEN '35-44' 
        WHEN _Custom57 between 45 and 54 THEN '45-54' 
    END as age

请仅count(*)在以下行中使用：

 , CAST(Round(count(r.RecipID) * 100.0 / count(*), 0) as nvarchar(5)) + '%' as RecipCount

推荐阅读