sql combine only some duplicates

Question

I want to combine one set of duplicates from my table, but not all.

example:

acct    date     bal
--------------------
123   1/1/2013   40.00
123   1/1/2013   2.00
456   1/2/2013   50.00
456   1/1/2013   5.00
789   1/1/2013   10.00
789   1/1/2013   17.00

I would like to combine acct 123 to only one row, summing the balance of those rows, but leave the rest.

desired output:

acct    date     bal
--------------------
123   1/1/2013   42.00
456   1/2/2013   50.00
456   1/1/2013   5.00
789   1/1/2013   10.00
789   1/1/2013   17.00

Working in SQL Server 2005.

Do you want to combine only the first duplicate row? Or on a particular date? Or on a particular acct? With what logic do you want to combine them? — Danielle Paquette-Harvey, Jan 15 '13 at 20:42
I want to combine on a particular acct. Summing the balance and pulling the highest date. — Matt, Jan 15 '13 at 20:49

score 3 · Answer 1 · answered Jan 15 '13 at 20:50

3

SELECT acct, date, SUM(bal)
FROM T
WHERE acct = 123

UNION

SELECT acct, date, bal
FROM T
WHERE acct <> 123

answered Jan 15 '13 at 20:50

MuhammadHani

7,729
4
26
44

and Alex whatever you said is true. That was one ridiculous piece of code... +1, simplicity pays off most of the time as the opposite likely to miss the obvious ;) – bonCodigo Jan 16 '13 at 07:03

score 3 · Accepted Answer · answered Jan 15 '13 at 21:46

3

Use CASE in GROUP BY clause

SELECT acct, date, SUM(bal) AS bal
FROM dbo.tets73
GROUP BY acct, date, CASE WHEN acct != 123 THEN bal END

Demo on SQLFiddle

answered Jan 15 '13 at 21:46

Aleksandr Fedorenko

15,209
6
35
42

This worked for the data I used in my example. Thank you very much. – Matt Jan 15 '13 at 22:03
@AlexanderFedorenko this is it - :D You got it all right, coz there has to be a simpler better way than union, that's what I was trying for ;) +1 This becomes one of my favourite answers! – bonCodigo Jan 16 '13 at 06:58
@bonCodigo tnx. I saw your answer, it was very close to the purpose;) – Aleksandr Fedorenko Jan 16 '13 at 08:47

score 2 · Answer 3 · answered Jan 15 '13 at 20:44

2

 select acct, date, sum(bal) from table where acct = 123
 union
 select acct, date bal from table where acct <> 123

answered Jan 15 '13 at 20:44

Satish

693
1
5
16

sql combine only some duplicates

3 Answers3