sql - cannot use SUM 'on an expression containing an aggregrate or a subquery'

I have the below code, which delivers between 1 and 3 results each time.

However, i wish to have the total value of the (up to) three results, rather than having them listed.

I can not simply wrap the whole coding in select sum () without getting the above error

开发者_StackOverflow

Any help greatfully appreicated

--

select 
case when sum(w.Value) > 3*sum(isnull (m.FFT1*m.FFL3V,0)
                        + isnull (m.FFT2*m.FFL3HCV,0)
                        + isnull (m.FFT3*m.FFFinanceSettleV,0)
                        + isnull (m.FFT4*m.FFFinanceSettleHCV,0)
                        + isnull (m.FFT5*m.FFL4V,0) 
                        + isnull (m.FFT6*m.FFL4HCV,0))
                        /count(w.ptmatter)

    then CAST (SUM(w.Value) AS NUMERIC(38,2))

else (
      select sum (isnull (m.FFT1*m.FFL3V,0) 
                 + isnull (m.FFT2*m.FFL3HCV,0)
                 + isnull (m.FFT3*m.FFFinanceSettleV,0)
                 + isnull (m.FFT4*m.FFFinanceSettleHCV,0)
                 + isnull (m.FFT5*m.FFL4V,0) 
                 + isnull (m.FFT6*m.FFL4HCV,0)) 
                 / count(w.ptmatter)) 
end

from dbo.workinprogress as w 
full join dbo.matterdatadef as m 
on (w.ptmatter = m.ptmatter) 
where (w.time <> 0) 
and  (w.ptclient= 
                 (select top 1 ptclient from workinprogress 
                                        where ptmatter=$matter$))
                                        and (m.lsccert = 
                                                      (select lsccert 
                                                      from matterdatadef 
                                                      where ptmatter=$matter$)
                                                      or (m.ptmatter=$matter$))
group by m.ptmatter 

---

What about wrapping the whole thing in a subquery?

SELECT SUM(v) FROM (
    select case when sum(w.Value) > 
        3*sum (
            isnull (m.FFT1*m.FFL3V,0) + 
            isnull (m.FFT2*m.FFL3HCV,0) +
            isnull (m.FFT3*m.FFFinanceSettleV,0) + 
            isnull (m.FFT4*m.FFFinanceSettleHCV,0) + 
            isnull (m.FFT5*m.FFL4V,0) + 
            isnull (m.FFT6*m.FFL4HCV,0)
        )/count(w.ptmatter)

    then CAST (SUM(w.Value) AS NUMERIC(38,2)) 
    else (
        select sum (
            isnull (m.FFT1*m.FFL3V,0) +
            isnull (m.FFT2*m.FFL3HCV,0) + 
            isnull (m.FFT3*m.FFFinanceSettleV,0) + 
            isnull (m.FFT4*m.FFFinanceSettleHCV,0) + 
            isnull (m.FFT5*m.FFL4V,0) + 
            isnull (m.FFT6*m.FFL4HCV,0)
        ) / count(w.ptmatter)) 
    end AS v

    from dbo.workinprogress as w 
    full join dbo.matterdatadef as m 
    on (w.ptmatter = m.ptmatter) 

    where (w.time <> 0) 
    and  (w.ptclient = (select top 1 ptclient from workinprogress where ptmatter=$matter$)) 
    and (m.lsccert = (select lsccert from matterdatadef where ptmatter=$matter$) 
    or (m.ptmatter=$matter$))

    group by m.ptmatter 
) as subQ