Error executing SUM - SQL Server

Question

Error executing SUM - SQL Server

Navigation

#1 by (0 votes)

0

Well, I'm trying to do SUM according to the query below, however, the following error occurs. "You can not perform an aggregate function on an expression that contains an aggregation or a subquery." The output is to exit this way.

Total_Horas   MesID        Mes     Ano
60             1         Janeiro   2018

Inquiry

select 
sum(dbo.FN_CALC_HORAS_UTEIS(s.SolData,min(l.LogData))),datepart(month,s.SolData) MesID,datename(month,s.soldata) Mes,datepart(year,s.soldata) Ano
from 
Solicitacao S
left join usuario U 
on (U.UsuID = S.UsuIDResponsavel) 
left join Status ST
on S.SolStatus = ST.CodStatus
left join Log L on L.LogSolID = s.SolID and (l.LOGDESCRICAO like '%1057%' or l.LOGDESCRICAO like '%3343%')
where S.proid in (2) and S.UsuIDResponsavel in(1776) and s.SolStatus <> 9 and convert(date,s.soldata) between '01-01-2018' and getdate()--and s.SolID = 65513
group by datepart(month,s.SolData),datename(month,s.soldata) ,datepart(year,s.soldata)

sql-server query

asked by anonymous 24.01.2018 / 13:27

1 answer

Redirect an address with javascript HTTP request giving error in Chrome and works on Edge and Firefox

score 0 · Answer 1

Try this with an external query:

select
    *,
    sum(horas_uteis) as soma_horas
from (
    select
        dbo.FN_CALC_HORAS_UTEIS(s.SolData,min(l.LogData)) as horas_uteis
        datepart(month,s.SolData) MesID,
        datename(month,s.soldata) Mes,
        datepart(year,s.soldata) Ano
    from Solicitacao S
        left join usuario U on (U.UsuID = S.UsuIDResponsavel) 
        left join Status ST on S.SolStatus = ST.CodStatus
        left join Log L on L.LogSolID = s.SolID and (l.LOGDESCRICAO like '%1057%' or l.LOGDESCRICAO like '%3343%')
    where
        S.proid in (2)
        and S.UsuIDResponsavel in (1776)
        and s.SolStatus <> 9
        and convert(date,s.soldata) between '01-01-2018' and getdate()--and s.SolID = 65513
) as X
group by MesID, Mes, Ano