SUM, Count, and convert - "cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Question

I am trying to convert a column that is a varchar i.e. "Alert" into a decimal so that I can divide the count of "Alert" by the SUM or total of "Alerts" to get the percentage of alerts by model type per day.... I have the following query, but I get the error "cannot perform an aggregate function on an expression containing an aggregate or a subquery”.

Answer 1

I think you simply want conditional aggregation:

select vehicle_model, date,
       sum(case when Alter = 'true' then 1 else 0 end) as num_alerts,
       avg(case when Alter = 'true' then 100.0 else 0 end) as percent_alerts
from MyTable
group by vehicle_model, date;

If you want the alerts in different rows, then:

select vehicle_model, date, alert,
       count(*) as cnt,
       count(*) * 100.0 / sum(count(*)) over (partition by vehicle_model, date) as percentage
from MyTable
group by vehicle_model, date, alert;

Answer 2

1. As the error suggests, you cannot directly perform an aggregate function on another aggregate.
2. To achieve this, you have to first count the results and then use the sum function in outer query.
3. Also use Sum() without groupby in outer query to get the sum of all the records.

with cte as(
select 
vehicle_model,
Alert
,count(Alert) as 'Count_of_Alerts'
,date
from MyTable
group by vehicle_model,date,Alert
)

select 
vehicle_model
,Alert
,date
,cast(Convert(decimal(18,2),(cast([Count_of_Alerts] as float)/cast(Summa as float)*100)) as varchar)+'%' as Percent_of_Alert
,[Count_of_Alerts]
from
(
    select c.vehicle_model,c.Alert,[Count_of_Alerts],Summa
    ,c.date
    from cte c
    left join
    (
    (select vehicle_model,date,Sum(cte.[Count_of_Alerts]) as Summa from cte group by vehicle_model,date)
    )A ON A.date=c.date AND A.vehicle_model=c.vehicle_model
)YY