CODEWITHSUNDEEP
phpmysql
Graham B
Graham B

Reputation: 39

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.

Upvotes: 1

Views: 2023

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28313

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

You're experiencing aggregate fanout issue. This happens whenever the primary table in a select query has fewer rows than a secondary table to which it is joined. The join results in duplicate rows. So, when aggregate functions are applied, they act on extra rows.

Here the primary table refers to the one where aggregate functions are applied. In your example,
* SUM(matters.fee) >> aggregation on table matters.
* SUM(advicetime*advicefee) >> aggregation on table actions
* fixedfee='Y' >> where condition on table matters

To avoid the fanout issue:
* Always apply the aggregates to the most granular table in a join.
* Unless two tables have a one-to-one relationship, don't apply aggregate functions on fields from both tables.
* Obtain your aggregates separately through different subqueries and then combine the result. This can be done in a SQL statement, or you can export the data and then do it.

Query 1:

SELECT SUM(fee) AS totfixed 
FROM matters 
WHERE fixedfee='Y'

Query 2:

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

Query 1 & Query 2 don't suffer from fanout. At this point you can export them both and deal with the result in php. Or you can combine them in SQL:

SELECT query_2.totbills, query_1.totfixed
FROM (SELECT SUM(fee) AS totfixed 
    FROM matters 
    WHERE fixedfee='Y') query_1,

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

Finally, SUM does not take a keyword DISTINCT. DISTINCT is only available to COUNT and GROUP_CONCAT aggregate functions. The following is a piece of invalid SQL

SUM(DISTINCT matters.fee) AS totfixed

Upvotes: 5

Related Questions