# Group two rows together by picking the Distinct Values

• ### Question

• Hi

There are many duplicate rows on the Table. where I need to pick the Distinct Values.

 Ref Adur Achg Bdur Bchg TotChg StartBal EndBal 1 0 0 10 0.1 0.1 10 0 1 10 0.05 0 0 0.05 0 9.85

Here I need to make these 2 rows into One Single Row for reporting purpose.

The Bolds values needs to be picked and and Sum the Totchg column.

I can only Identify it a Duplicate row bcos the Ref column has the same IDs.

My Result must be like.

 Ref Adur Achg Bdur Bchg TotChg StartBal EndBal 1 10 0.05 10 0.1 0.15 10 9.85

shan

Friday, October 22, 2010 7:00 PM

• SELECT Ref, SUM(Adur) as Adur, SUM(achg) as Achg, SUM(Bdur) as Bdur, SUM(Bchg) as Bchg,  SUM(totchg) as Totchg, SUM(StartBal) as StartBal, SUM(endbal) as EndBal

from table

group by ref

• Edited by Friday, October 22, 2010 7:04 PM typo
• Proposed as answer by Friday, October 22, 2010 7:06 PM
• Marked as answer by Thursday, October 28, 2010 2:55 PM
Friday, October 22, 2010 7:03 PM

### All replies

• SELECT Ref, SUM(Adur) as Adur, SUM(achg) as Achg, SUM(Bdur) as Bdur, SUM(Bchg) as Bchg,  SUM(totchg) as Totchg, SUM(StartBal) as StartBal, SUM(endbal) as EndBal

from table

group by ref

• Edited by Friday, October 22, 2010 7:04 PM typo
• Proposed as answer by Friday, October 22, 2010 7:06 PM
• Marked as answer by Thursday, October 28, 2010 2:55 PM
Friday, October 22, 2010 7:03 PM
• Hi

Sorry i forgot to mention like - I shouldnt sum the StartBal and EndBal. It should Pick the Bold once - It may below value may be 3 or 5 -It should only pick 10 as StartBal and 9.85 on EndBal.

Thanks a lot.

Saturday, October 23, 2010 8:18 AM
• Try following else if you could provide clarification

```SELECT Ref, SUM(Adur) as Adur, SUM(achg) as Achg, SUM(Bdur) as Bdur, SUM(Bchg) as Bchg, SUM(totchg) as Totchg, SUM( case when StartBal = 10.5 then 10.5 else 0 end) as StartBal, SUM(case when endbal = 9.85 else 0 end) as EndBal

from table

group by ref

```
Saturday, October 23, 2010 11:48 AM
•

```select Ref, max(Adur) as Adur, Max(Achg) as Achg, Max(BDur) as Bdur,

max(Bchg) as Bchg, sum(TotChg) as TotChg, max(StartBal) as StartBal, Max(EndBal) as EndBal

GROUP BY Ref
```

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
• Proposed as answer by Sunday, October 24, 2010 11:00 AM
Sunday, October 24, 2010 12:50 AM