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.

解决方案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