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.
| Author |
Topic |
|
ke c
Starting Member
2 Posts |
 Posted - 2012-02-14 : 02:16:13
|
| i've to generate a notepad using this query in vb.net : strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _ "LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _ "d.BankCode, (select count(*) from (select ShareholderId from dividend " & _ "where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _ "BnkAccount, BankCode ) dividend) as Temps " & _ "from dividend d join Bank b on d.BankCode = b.BankCode " & _ "group by d.ShareholderId, d.UsufructId, d.BnkAccount,d.BankCode,d.TitleCode,d.Forename,d.Surname," & _ "b.SBMCode " & _ "ORDER BY d.BankCode, d.ShareholderId"In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount. My question is, how to group the "d.amount" without having repetition in the "d.shareholderid" ?? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-14 : 06:53:37
|
| [code]strSql = "Select count(*),SUM(d.Amount), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _[/code]That will sum up the amounts for each SharedholderId (and UsufructId, BankAccount and the other columns in the group by clause). |
 |
|
|
ke c
Starting Member
2 Posts |
Posted - 2012-02-14 : 23:46:56
|
| Actually i also need a breakdown of each amount for a shareholder. And a shareholder might have 2 different amount. So i need to sum up the amount per shareholder id and group it.Thx for helping. :) |
|
|
|
|
|
|
|
|
REMOVING DUPLICATE ROWS IN GROUPING