Many to Many record selection - 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

General SQL Server Forums

New to SQL Server Programming

Many to Many record selection

Author

Topic

vksundari208
Starting Member

1 Post

Posted - 2009-03-15 : 22:01:36

CUSTOMER_ID, ACCOUNT_ID, AVAILABLE_FUND,EXPORT_ACCOUNT_ID
54546720003, MB 7136374852, 185, TB 5046649830
54546720003, MB 7136374852, 85, TB 5777775788
54546720003, MB 7136374852, 120, TB 4776884454
54546720003, MB 7135391693, 185, TB 5046649830
54546720003, MB 7135391693, 85, TB 5777775788
54546720003, MB 7135391693, 120, TB 4776884454

My example data is as listed above.

I could find the collective available_fund of EXPORT_ACCOUNT_ID when ACCOUNT_ID is multiple and EXPORT_ACCOUNT_ID is single for the Same CUSTOMER_ID. Same way for single ACCOUNT_ID and multiple EXPORT_ACCOUNT_ID.

Can any one please help on how to find only the collective AVAILABLE_FUND when both the accounts are multiple

Venkat

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-03-16 : 00:04:25

TRY THIS:-
i dont have SQL Server to execute query here... so just a logical guess from my side. Hope it works

select sum(AVAILABLE_FUND) from TABLE group by ACCOUNT_ID having count(ACCOUNT_ID)>1 and count(EXPORT_ACCOUNT_ID)>1

Regards,
Sumit Batra
Software Engineer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:39:27

if sql 2005 or higher


SELECT CUSTOMER_ID, ACCOUNT_ID, AVAILABLE_FUND,EXPORT_ACCOUNT_ID,SUM(AVAILABLE_FUND) OVER (PARTITION BY CUSTOMER_ID) AS Collective_Fund FROM Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources