SQL SUM logical Error

Question

I have an issue with SUM on an SQL statement. I am using MSSQL 2014 version and Management Studio Version 12.0.2000.8.

I am trying to get the Dividends for each individual from a table based on their ratio contributions and a 60% of the Total Income. Whenever I use SUM(TotalShares) I get all the Share Holders having the same dividends (Total dividends) as shown

    SELECT Names, MemGrpID, GtotalIncome, TotalShares, (GTotalIncome*0.6)*
    ((TotalShares)/SUM(TotalShares)) AS Dividends FROM tblDivedends GROUP BY 
    Names, MemGrpID, GTotalIncome, TotalShares

When I do the above I ONLY get each Name with same dividends (total dividends) and that is wrong.

**Names               MemGrpID      GTotalIncome   TotalShares  Dividends**
*Kevin Onyango Otieno  8484         30260.97       8000         18456.582000
*Paul Anam Otieno     6060          30260.97       5000         18456.582000
*Simon Kimani         8526          30260.97       1000         18456.582000

However, if is just put the figure of the TotalShares directly or manually (i.e. 14000), I get correct allocation.

    SELECT Names, MemGrpID, GtotalIncome, TotalShares, (GTotalIncome*0.6)*
    ((TotalShares)/SUM(14000.00)) AS Dividends FROM tblDivedends GROUP BY 
    Names, MemGrpID, GTotalIncome, TotalShares

Names                 MemGrpID  GTotalIncome  TotalShares   Dividends
*Kevin Onyango Otieno  8484     30260.97      8000          10375.179339
*Paul Anam Otieno     6060      30260.97      5000          6484.478009
*Simon Kimani         8526      30260.97      1000          1296.888339

BTW if I

SELECT SUM(TotalShares) As TotS from tblDivedends

I get correct result as 14000.00.

score 0 · Answer 1 · answered Jun 12 '17 at 08:43

0

Add the total directly as a subquery,if it`s the same query your GROUP BY takes effect and you get wrong results.

 SELECT Names, MemGrpID, GtotalIncome, TotalShares, (GTotalIncome*0.6)*
    ((TotalShares)/SELECT SUM(TotalShares) from tblDivedends) AS Dividends FROM tblDivedends GROUP BY 
    Names, MemGrpID, GTotalIncome, TotalShares

answered Jun 12 '17 at 08:43

Mihai

26,325
7
66
81

Hi Mihai, Fantastic, your method also works very well. Sub queries rock! Thank you all. Stackoverflow forever!!! Would be more than happy to upvote you. Way of doing that? Thanks – Simon Jun 13 '17 at 08:16

score 0 · Answer 2 · answered Jun 12 '17 at 09:28

0

Another way of doing it is by partion:

 SELECT Names, MemGrpID, GtotalIncome, TotalShares, (GTotalIncome*0.6)*
    ((TotalShares)/SUM(TotalShares) over(partition by 1)) AS Dividends FROM tblDivedends GROUP BY 
    Names, MemGrpID, GTotalIncome, TotalShares

answered Jun 12 '17 at 09:28

Jayvee

10,670
3
29
40

Great, your methods works great! Thanks a million at least I am not pulling off my hairs!!! Is there a way of "Upvoting" you if such word exists? – Simon Jun 13 '17 at 08:15
glad to hear it helped, you can upvote by clicking on the arrow above the points and by accepting the answer through the button. Thanks! – Jayvee Jun 13 '17 at 08:25

SQL SUM logical Error

2 Answers2