sql - T-Sql组/求和查询题

Question

这是一个糟糕的星期一上午，我无法理清思路。有人可以帮我弄清楚如何对返回的行进行分组/求和，以便只有一个 AssessorParcelNumber 实例吗？

所以，而不是下面的结果集:

140-31-715-164  3545    2004-09-14 00:00:00.000 1665.00 0.00    0.00    1665.00
140-31-715-164  3545    2004-09-14 00:00:00.000 0.00    534.00  0.00    534.00
140-31-715-037  3546    2004-03-11 00:00:00.000 120.00  0.00    0.00    120.00
140-31-715-037  3546    2004-03-11 00:00:00.000 0.00    0.00    0.00    0.00

我得到这个:

140-31-715-164  3545    2004-09-14 00:00:00.000 1665.00 534.00  0.00    2199.00
140-31-715-037  3546    2004-03-11 00:00:00.000 120.00  0.00    0.00    120.00

帮助!谢谢!

select
      u.AssessorParcelNumber,
      c.CollectionKey AS [r_number],
      c.Closed,
      CASE cd.Name1 WHEN 'Association'
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [assoc_balance],
      CASE cd.Name1 WHEN 'RRFS' 
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [rr_balance],
      CASE cd.Name1 WHEN 'RRFS' THEN 0 WHEN 'Association' THEN 0
            ELSE CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) END AS [_balance],
      CONVERT(dec(18,2),SUM(t.amount - t.AppliedAmount)) AS [balance]
from
      Unit u with(nolock)
      left outer join [collection] c with(nolock) on u.UnitKey = c.UnitKey
      left outer join TransactionDetail t with(nolock) on c.CollectionKey=t.CollectionKey
      left outer join TypeCode tc with(nolock) on t.PostType = tc.PostType
      left outer join CodeData cd with(nolock) on tc.Category = cd.Code2 and Code1=5
where
      t.Credit = 0 -- is a charge
      and t.Voided = 0 -- is not voided
      -- and u.AssessorParcelNumber = '140-31-715-164'
group by
      u.AssessorParcelNumber, c.CollectionKey, c.closed, cd.Name1
order by
      c.CollectionKey,
      cd.Name1;

Answer 1

看起来您想要对各种余额列求和。

SELECT
      t.AssessorParcelNumber,
      t.[r_number],
      t.Closed,
      SUM([assoc_balance]),
      SUM([rr_balance]),
      SUM([_balance]),
      SUM([balance])
    FROM (/* Insert your original query here */) t
    GROUP BY t.AssessorParcelNumber, t.r_number, t.Closed