Return SQL results with Percent

Question

I have the following query and with the count needed to return the result set in the data like 25%.

I keep receiving the following error when trying to run my query:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

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

Stop splattering your code with [nolock hints](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). But if you do need them, at least don't use the deprecated syntax. — SMor, Aug 24 '20 at 20:08

score 0 · Answer 1 · answered Aug 24 '20 at 18:02

0

I think there are two issues:

Please use the biggest value first otherwise all 65+ will end up in 55-64 category too.

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

Please use only count(*) in the following line:

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

answered Aug 24 '20 at 18:02

sacse

3,634
2
15
24

When changing to count (*) the following error is thrown when trying to run the query "Operand data type nvarchar is invalid for sum operator." – ATX_Jordan Aug 24 '20 at 18:17
@SMor please cast RecipCount as well inside pivot like cast(RecipCount AS int). – sacse Aug 25 '20 at 03:24

Return SQL results with Percent

1 Answers1