Results 1 to 5 of 5
Thread: sum within a sum

041914, 20:32 #1Registered User
 Join Date
 Feb 2014
 Posts
 35
Unanswered: sum within a sum
I am using mySQL.
I have a table called outcomes which has the following fields  moid, outcomeid, finance, multiplier, backdated and projected.
Finance is a decimal and both backdated and projected are each 1 or 0 depending on whether an outcome is backdated, projected, both or neither.
To get the financial value of an award the finance field is multiplied by the multiplier field. I need to get 3 different totals out into a table:
1 A total of the awards which are backdated (i.e. sum of finance*multiplier*backdated)
2 A total of the awards which are projected (i.e. sum of finance*multiplier* projected)
3 A total of the awards which are neither backdated or projected (i.e. sum of finance*multiplier less the two totals above.
I can use this:
SELECT matteroutcomes.outcomeid, SUM(matteroutcomes.finance), SUM(finance*multiplier*projected) AS proj, SUM(finance*multiplier*backdated) AS backd
FROM matteroutcomes
GROUP BY outcomeid
which gives me the totals but to get the correct total for number 3, I need to subtract the second two from the first. I tried:
SELECT matteroutcomes.outcomeid, SUM(SUM(matteroutcomes.finance)SUM(finance*multiplier*projected)SUM(finance*multiplier*backdated)) AS tott
FROM matteroutcomes
GROUP BY outcomeid
but this gave an error.
Can someone tell me how to do this or give me some pointers.
Sorry if I’m not being very clear.

042014, 02:40 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Taking a radically different path, I'd try:
Code:SELECT matteroutcomes.outcomeid , Sum(CASE WHEN 1 = backdated THEN finance * multiplier END) AS Backdated , Sum(CASE WHEN 1 = projected THEN finance * multiplier END) AS Projected , Sum(CASE WHEN 0 = backdated AND 0 = projected THEN finance * multiplier END) AS Ordinary FROM matteroutcomes GROUP BY outcomeid
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

042014, 03:15 #3Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483
3 A total of the awards which are neither backdated or projected (i.e. sum of finance*multiplier less the two totals above.
I can use this:
SELECT matteroutcomes.outcomeid, SUM(matteroutcomes.finance), SUM(finance*multiplier*projected) AS proj, SUM(finance*multiplier*backdated) AS backd
FROM matteroutcomes
GROUP BY outcomeid
In the description: "(i.e. sum of finance*multiplier ..."
In the SQL code: "SUM(matteroutcomes.finance), "Last edited by tonkuma; 042014 at 03:22.

042014, 05:57 #4Registered User
 Join Date
 Feb 2014
 Posts
 35
Pat's reply worked just as I wanted  many thanks.

042014, 10:17 #5Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483
If Pat's reply worked,
this may work too.
Code:SELECT outcomeid , SUM( finance * multiplier * projected ) AS proj , SUM( finance * multiplier * backdated ) AS backd , SUM( finance * multiplier * (1  projected) * (1  backdated) ) AS tott FROM matteroutcomes GROUP BY outcomeid