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

Transact-SQL (2008)

Syntex Help...

Author

Topic

rajnag1
Starting Member

2 Posts

Posted - 2010-12-23 : 07:57:07

I have table with columns say

Branch - Amt - Due

AAA-200-10
BBB-250-12
CCC-300-10
AAA-100-0
BBB-10-0
CCC-250-0

Here my question is, Want to group the columns on Branch, Sum of Loan Amt, Sum of Amt where due is greater then 0,
Answer should be some thing like :
AAA - 300 - 200
BBB -260 - 250
CCC - 550 - 300

Any help will be greately appreciated.

Rgds,
Raju

Surfer513
Starting Member

29 Posts

Posted - 2010-12-23 : 08:50:11

The below select statement with an embedded select statement is tested and works. It looks a little 'spaghetti' like, so I'm sure there is a nicer, more efficient way to go about this. But this is successful in what you want:

SELECT s.branch, s.total_amt, s.total_due
FROM
(SELECT branch, SUM(amt) as total_amt, SUM(due) as total_due
FROM loantable
GROUP BY branch) s
WHERE s.total_due > 0

I'm sure one of the experts will chime in with the best way to do this.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-23 : 08:51:29

select Branch
,[Amt] = sum(amt)
,[due] = sum(case when due >0 then Amt else 0 end)
from yourTable

group by Branch

Jim

Everyday I learn something that somebody else already knew

Surfer513
Starting Member

29 Posts

Posted - 2010-12-23 : 12:00:49

Jim, I don't believe that the result of your select statement fulfills the original poster's requirements. With that select statement, any group by that the branch has no due value (set to 0) it still will return that. My understanding is that if the branch has no due that it gets filtered out, which is why I did the embedded select statement and the parent statement having the where clause.

Raju, if I am misunderstanding your requirement then please let me know.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-23 : 12:22:25

Jim's solution should give exactly the posted/wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-23 : 12:25:16

The OP is a bit unclear to me as to his exact requirements. But if your interpretation is correct,
SELECT branch, SUM(amt) as total_amt, SUM(due) as total_due
FROM loantable
GROUP BY branch
HAVING sum(due) > 0 would do the same thing.

My query produces the OP's desired output. Hopefully he'll let us know!

Jim

Everyday I learn something that somebody else already knew

Surfer513
Starting Member

29 Posts

Posted - 2010-12-23 : 16:31:37

Jim, I agree with you that the HAVING clause is much better than my original solution (provided my interpretation of the OP is correct).

rajnag1
Starting Member

2 Posts

Posted - 2010-12-24 : 13:09:39

Thanx. Jim, It's really worked great.

Merry Christmas.

quote:
Originally posted by jimf

select Branch
,[Amt] = sum(amt)
,[due] = sum(case when due >0 then Amt else 0 end)
from yourTable

group by Branch

Jim

Everyday I learn something that somebody else already knew

Rgds,
Raju

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources