How to perform duplicate rows addition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to perform duplicate rows addition

Author

Topic

arunkumar09288
Starting Member

1 Post

Posted - 2010-12-28 : 06:03:06

i want similar records of counting from two tables.nw i had this type of result shown below.
Brandid toatl
2175 95
1391 80
733 50
1022 49
983 43
2285 43
763 39
464 38
1416 17
2175 17
1391 15
2432 15
2000 13
464 8
763 8
271 6
1337 6
2427 6
1022 5
1337 5
733 4
1342 4
233 3
352 3
983 3
2264 3
1416 2
13 1
142 1
206 1
233 1
271 1
275 1
384 1
552 1
1680 1
1924 1
2000 1
2081 1
2285 1
2432 1
3504 1
3615 1

from above i want duplicate rows of sum of taotal in one field.
for example take 2175 record it is repeated 2 times.so i want 2175 taotal is 95+17=112.how can i achive this?

for above result i assumed query like this

select distinct (od.Brand_Id), total=(count(od.Offer_Id)) from tblm_offers_daily od
where CategoryPath like 'Cars%' and Geography_Id=4
group by(od.Brand_Id )
union
select Brand_Id, count(Offer_Id) as total from tblm_offers_history
where CategoryPath like 'Cars%' and Geography_Id=4
group by(Brand_Id)
order by total desc

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 06:06:58

select Brandid,total,sum(Brandid)over(partition by total)as duplicatetotal from yourtable

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-29 : 21:34:22

[code]
select Brand_Id, total = sum(total)
from
(
select distinct (od.Brand_Id), total=(count(od.Offer_Id))
from tblm_offers_daily od
where CategoryPath like 'Cars%' and Geography_Id=4
group by(od.Brand_Id )
union
select Brand_Id, count(Offer_Id) as total from tblm_offers_history
where CategoryPath like 'Cars%' and Geography_Id=4
group by(Brand_Id)
) a
group by Brand_Id
order by total desc[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources