Results 1 to 5 of 5
  1. #1
    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 Im not being very clear.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Taking 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
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    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
    Is it neccesary to multiply by multiplier for number 3?
    In the description: "(i.e. sum of finance*multiplier ..."
    In the SQL code: "SUM(matteroutcomes.finance), "
    Last edited by tonkuma; 04-20-14 at 04:22.

  4. #4
    Join Date
    Feb 2014
    Posts
    35
    Pat's reply worked just as I wanted - many thanks.

  5. #5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •