Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2014
    Posts
    35

    Unanswered: MySQL SUM() giving incorrect total

    I am developing a php/mysql database. I have two tables - matters and actions.
    Amongst other fields the matter table contains 'matterid' 'fixedfee' and 'fee'. Fixed fee is Y or N and the fee can be any number.

    For any matter there can be a number of actions. The actions table contains 'actionid' 'matterid' 'advicetime' 'advicefee'. The advicetime is how long the advice goes on for (in decimal format) and advicefee is a number. Thus, to work out the cost of the advice for a matter I use SUM(advicetime*advicefee).

    What I wish to do is to add up all of the 'fee' values when 'fixedfee'=Y and also the sum of all of the SUM(advicetime*advicefee) values for all of these matters.

    I have tried using:

    SELECT SUM(matters.fee) AS totfixed, SUM(advicetime*advicefee) AS totbills, FROM matters INNER JOIN actions ON matters.matterid=actions.matterid WHERE fixedfee = 'Y'

    but this doesn't work as (I think) it is adding up the matters.fee for every time there is an action. I have also tried making it

    SUM(DISTINCT matters.fee) AS totfixed
    but this doesn't work as I think it seems to be missing out any identical fees (and there are several matters which have the same fixed fee).

    I am fairly new to this so any help would be very welcome.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Solve one calculation at a time, separately.
    Post back your code and we can help you stitch the two together!
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    don't forget you can use case statements for individual summations as well. Like:
    Code:
    select colA
    ,sum(case when fixedfee = 'y' then col_to_be_summed else null end) * ....
    from my_table
    where....
    Dave

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to me the data model has the whiff of a non normalised design.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2014
    Posts
    35
    I have used:

    SELECT FORMAT((SUM(advicetime*advicefee)),0) AS billfee FROM matters INNER JOIN actions ON matters.matterid=actions.matterid WHERE fixedfee = 'Y'

    and

    SELECT FORMAT(SUM(matters.fee),0) AS feetot FROM matters WHERE fixedfee = 'Y'

    both of which work individually. It's when I put them together in one query that it goes wrong.

  6. #6
    Join Date
    Feb 2014
    Posts
    35
    I have used:

    SELECT FORMAT((SUM(advicetime*advicefee)),0) AS billfee FROM matters INNER JOIN actions ON matters.matterid=actions.matterid WHERE fixedfee = 'Y'

    and

    SELECT FORMAT(SUM(matters.fee),0) AS feetot FROM matters WHERE fixedfee = 'Y'

    both of which work individually. It's when I put them together in one query that it goes wrong.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this:
    Code:
    SELECT x.feetot
         , y.billfee
    FROM   (
            SELECT Sum(fee) As feetot
            FROM   matters
            WHERE  fixedfee = 'Y'
           ) As x
     CROSS
      JOIN (
            SELECT Sum(advicetime * advicefee) As billfee
            FROM   actions
            WHERE  EXISTS (
                     SELECT *
                     FROM   matters
                     WHERE  fixedfee = 'Y'
                     AND    matterid = actions.matterid
                   )
           ) As y
    I've eliminated the join that is causing you problems (by using an EXISTS clause)
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think I know what your issue was with your original merge. Probably the inner join has to be an outer, like:
    Code:
    SELECT FORMAT((SUM(advicetime*advicefee)),0) AS billfee 
           ,FORMAT(SUM(matters.fee),0) AS feetot
       FROM matters 
    left outER JOIN actions
      ON matters.matterid=actions.matterid 
    WHERE fixedfee = 'Y'
    Dave

Posting Permissions

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