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

 

Can anyone please help?

Thanks a lot in advance.

shan

Answer 1

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

Answer 2

Hi

Thanks for your reply.

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.

 

Answer 3

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

Answer 4

 

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

from your table

GROUP BY Ref

 

---

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

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog