query help - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

query help

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-09-27 : 03:49:42

How can i write this query ?

TABLE

CODE AMOUNT
100 20
100.01 10
100.01.10 7

OUTPUT

CODE AMOUNT
100 37
100.01 17
100.01.10 7

Thanks for answers

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 08:27:18

[code]select
code,
sum(amount) over(order by len(code) desc) as Amount
from
yourTable;[/code]

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-09-27 : 11:33:07

quote:

Originally posted by James K

select
	code,
	sum(amount) over(order by len(code) desc) as Amount
from
	yourTable;

Thanks for answer but im not using your query this table.how can i write this query another way ?

TABLE

CODE AMOUNT
100 20
100.01 10
100.01.10 7
200 10
200.01 6

OUTPUT

CODE AMOUNT
100 37
100.01 17
100.01.10 7
200 16
200.01 6

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 11:43:09

[code]select
code,
sum(amount) over(partition by LEFT(code,3) order by len(code) desc) as Amount
from
yourTable;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources