Hi,
I have a sybase table called 'AccountOwners' which looks as follows:
InvestorId RegisteredAccId Priority
----------- --------------- -----------
32 101165 0
38 100011 0
39 100011 1
The logic behind this table is as follows: a single registered account, identified by the 'RegisteredAccId' field can have one or more investors, each of which is represented by an 'investorid'.
i.e. see in the above information that the RegisteredAccId 100011 has 2 account investors represented by investorid 38 and 39. Obviously the data above is only a snippet of all the information, and there exist many accounts that have more than one investors. An account can have up to 4 investors. The Prioity field, as the name implies, prioritizes the investors of an account from 0 to 4.
As part of a java method, I wish to write embedded SQL that will query this table to return all account that have more than one investors and then count the number of investors and return this as an integer. I know I can do the first part by using the priority field in the table since any account that has 'priority > 0' must have more than one investor:
"select * from AccountOwners ao where ao.Priority > 0"
This will return all accounts that have more than one investor.
However, I now wish to count the number of investors that each account has and return this number in the following format:
No.Investors RegisteredAccId
----------- ---------------
i.e. - in the above example, for RegisteredAccId 100011, the return would be....
No.Investors RegisteredAccId
----------- ---------------
2 100011
.....because it has 2 investors (investorid 38 and 39).
I'm not sure how to do this count though and return an integer. Could someone please help?
Cheers,
I have a sybase table called 'AccountOwners' which looks as follows:
InvestorId RegisteredAccId Priority
----------- --------------- -----------
32 101165 0
38 100011 0
39 100011 1
The logic behind this table is as follows: a single registered account, identified by the 'RegisteredAccId' field can have one or more investors, each of which is represented by an 'investorid'.
i.e. see in the above information that the RegisteredAccId 100011 has 2 account investors represented by investorid 38 and 39. Obviously the data above is only a snippet of all the information, and there exist many accounts that have more than one investors. An account can have up to 4 investors. The Prioity field, as the name implies, prioritizes the investors of an account from 0 to 4.
As part of a java method, I wish to write embedded SQL that will query this table to return all account that have more than one investors and then count the number of investors and return this as an integer. I know I can do the first part by using the priority field in the table since any account that has 'priority > 0' must have more than one investor:
"select * from AccountOwners ao where ao.Priority > 0"
This will return all accounts that have more than one investor.
However, I now wish to count the number of investors that each account has and return this number in the following format:
No.Investors RegisteredAccId
----------- ---------------
i.e. - in the above example, for RegisteredAccId 100011, the return would be....
No.Investors RegisteredAccId
----------- ---------------
2 100011
.....because it has 2 investors (investorid 38 and 39).
I'm not sure how to do this count though and return an integer. Could someone please help?
Cheers,