SQL server Calculate with COUNT

Question

    select
     category, count(category) as 'TotalCounts',
     COUNT(case kind when 'avail'then 1 else null end) as 'avail',
     Count(case kind when 'offers' then 1 else null end) as 'offers',
     COUNT(CASE contactMethod  WHEN 'SMS' then 1 else null END) as 'SMS',
     COUNT(case contactMethod when 'call' then 1 else null end) as 'call',

     CONVERT(varchar(254),COUNT (case when max_biz_status='A' OR 
     max_biz_status ='B' then 1 else null end) * 100 / count(category)) +'%' 
     as 'Percetange'

     from reports
     group by category
     order by TotalCounts

Instead of calculating again in Convert method i want to use avail* 100 / TotalCounts like i did in order by when i used TotalCounts.

i tried:

 CONVERT(varchar(254),avail * 100 / TotalCounts) +'%' as 'Percetange'

but i get 'invalid column name' for avail and TotalCounts

Scope issue. That column TotalCounts isn't available in the same select list. Either repeat the aggregate function, or have a derived table. — jarlh, Aug 20 '18 at 14:41

score 2 · Answer 1 · answered Aug 20 '18 at 14:44

You can't use avail or TotalCounts as you just created them, so they aren't in scope, using a common-table expression is one way to fix this:

WITH cte AS (
    SELECT
         category, 
         COUNT(category) AS TotalCounts,
         COUNT(case kind WHEN 'avail' THEN 1 ELSE NULL END) AS avail,
         COUNT(case kind WHEN 'offers' THEN 1 ELSE NULL END) AS offers,
         COUNT(CASE contactMethod  WHEN 'SMS' THEN 1 ELSE NULL END) AS SMS,
         COUNT(case contactMethod WHEN 'call' THEN 1 ELSE NULL END) AS [call]
    FROM 
        reports
    GROUP BY 
        category)
SELECT
    *,
    CONVERT(varchar(254),avail * 100 / TotalCounts) +'%' AS Percetange --(sic)
FROM
    cte
ORDER BY 
    TotalCounts;

score 2 · Accepted Answer · answered Aug 20 '18 at 14:47

You can't do that because your TotalCounts column is made from your result set.

you can try to use a subquery to contain it then calculation.

if your mssql version support CONCAT function you can use it let the SQL clearer.

 SELECT t1.*,CONCAT((max_biz_statusCnt * 100 /TotalCounts),'%')as 'Percetange'
 FROM 
 (
    select
     category, 
     count(*) as 'TotalCounts',
     COUNT(case kind when 'avail'then 1 else null end) as 'avail',
     Count(case kind when 'offers' then 1 else null end) as 'offers',
     COUNT(CASE contactMethod  WHEN 'SMS' then 1 else null END) as 'SMS',
     COUNT(case contactMethod when 'call' then 1 else null end) as 'call',
     COUNT (case when max_biz_status='A' OR  max_biz_status ='B' then 1 else null end) 'max_biz_statusCnt'
    from reports
     group by category
 ) t1
 order by TotalCounts

SQL server Calculate with COUNT

2 Answers2