sql-不能在包含聚合或子查询的表达式上使用SUM'

Question

我有下面的代码，每次都有1到3个结果。

但是，我希望有（最多）三个结果的总价值，而不是让他们列出。

我不能简单地包裹在select sum()整个编码没有得到上面的错误

任何帮助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 

Answer 1

约在子查询包裹整个事情是什么？

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