Where in max running total value

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

Transact-SQL (2008)

Where in max running total value

Author

Topic

gecew
Starting Member

10 Posts

Posted - 2012-04-18 : 12:06:22

Sid In Out
01 1 0
02 3 1
01 0 1

i want to "select Sid where max (SUM(In)-SUM(Out))>0 "

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-18 : 13:06:56

Not 100% sure what you are asking. Would this be it?

SELECT TOP (1) WITH TIES SID
FROM YourTable
GROUP BY SID
HAVING SUM([In])-SUM([Out]) > 0
ORDER BY SUM([In])-SUM([Out]) DESC

Post your expected output with some sample data that is more representative of the problem you are trying to solve.

gecew
Starting Member

10 Posts

Posted - 2012-04-18 : 14:31:07

works perfect thx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-18 : 14:45:00

Cool!! You are very welcome.

gecew
Starting Member

10 Posts

Posted - 2012-05-01 : 10:26:34

how to this query works in subquery i try and get error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 10:42:28

This is happening probably because there is more than one SID which has the same rank. If you remove the "WITH TIES", that will cause only one SID to be returned. However, if there are ties, and if you want to pick a specific one out of those, you should add additional conditions (in the order by clause).

SELECT TOP (1) WITH TIES SID
FROM YourTable
GROUP BY SID
HAVING SUM([In])-SUM([Out]) > 0
ORDER BY SUM([In])-SUM([Out]) DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:43:15

quote:
Originally posted by gecew

how to this query works in subquery i try and get error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

post your query first
We cant make out how exactly you're using from description above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gecew
Starting Member

10 Posts

Posted - 2012-05-01 : 11:16:34

i remove "with ties" and running :) thx all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources