All geek questions in one place

T-Sql group / sum query question

It was crappy Monday and I can't think straight. Can someone help me figure out how to group / summarize the returned rows so that there is only one instance of the AssessorParcelNumber class?

So, instead of the following result set:

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

I get this instead:

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

Help! Thank!


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;
+3
source share
5 answers

It looks like you want to SUM your various balance columns.

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
+4
source

Assuming SQL Server 2005 or better:

I would use your current query as CTE, then query / group. I.e:.

;With CTE AS(


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)


SELECT AssessorParcelNumber, 
r_number, 
Closed, 
SUM(Assoc_balance) AS 'Assoc_Balance',
SUM(rr_balance) AS 'rr_balance',
SUM(_balance) AS '_balance',
SUM(balance) AS 'balance'
FROM CTE
GROUP BY AssessorParcelNumber, r_number, Closed
+1
source

, , , GROUP BY cd.Name1 SUM(CASE...) .

:

CAST(SUM(CASE cd.Name1
             WHEN 'Association' THEN t.Amount - t.AppliedAmount
             ELSE 0
         END) AS DECIMAL(18, 2)) AS [assoc_balance],
CAST(SUM(CASE cd.Name1
             WHEN 'RRFS' THEN t.Amount - t.AppliedAmount
             ELSE 0
         END) AS DECIMAL(18, 2)) AS [rr_balance],

CAST(SUM(CASE cd.Name1
             WHEN 'RRFS' THEN 0
             WHEN 'Association' THEN 0
             ELSE t.Amount - t.AppliedAmount
         END) AS DECIMAL(18, 2)) AS [_balance],

, ORDER BY.

0
source 
select
      u.AssessorParcelNumber,
      c.CollectionKey AS [r_number],
      c.Closed,
      [assoc_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='Association' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      [rr_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='RRFS' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      [_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='RRFS' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      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;
0
source

The problem is with the inclusion of cd.Name1 in the GROUP BY clause. In the list of results that you indicated (and I make some assumptions here), you have each line for cd.Name = "Association" and "RRFS", and the group resets it to two lines. Take this column from GROUP BY and move it to the case statement, for example:

select 
      u.AssessorParcelNumber, 
      c.CollectionKey AS [r_number], 
      c.Closed, 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'Association' THEN t.Amount - t.AppliedAmount ELSE 0 END)) AS [assoc_balance], 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'RRFS'        THEN t.Amount - t.AppliedAmount ELSE 0 END)) AS [rr_balance], 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'RRFS' THEN 0 WHEN 'Association' THEN 0 ELSE 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
order by 
      c.CollectionKey, 
      cd.Name1;
0
source

Source: https://habr.com/ru/post/1770158/

More articles:


All Articles