MySQL SUM()给出不正确的总数 [英] MySQL SUM() giving incorrect total

问题描述

我正在开发一个php/mysql数据库.我有两个表格-问题和行动. 在其他字段中，问题表包含杂项"，"fixedfee"和"fee".固定费用是Y或N，费用可以是任意数字.

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.

任何事情都可以采取许多措施.操作表包含"actionid"，"matterid"，"advicetime"，"advicefee".咨询时间是咨询持续的时间(以十进制格式)，而advicefee是一个数字.因此，为了计算出建议的成本，我使用了SUM(advicetime * advicefee).

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

我希望做的是将'fixedfee'= Y时的所有'fee'值加起来，并将所有这些问题的所有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.

我尝试使用:

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

但是这不起作用，因为(我认为)它是在每次执行操作时都会加总thess.fee.我也尝试过

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.

推荐答案

但是这不起作用，因为(我认为)它是在每次执行操作时都会加总thess.fee.我也尝试过使它...

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

您遇到的是aggregate fanout issue.这种情况发生在每当选择查询主表具有比其所接合辅助表更少的行.联接导致重复的行.因此，当应用聚合函数时，它们将作用于额外的行.

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.

此处主表是指应用聚合函数的表.在您的示例中，
* SUM(matters.fee) >>在表matters上的聚合.
* SUM(advicetime*advicefee) >>表actions
上的聚合 * fixedfee='Y' >>表matters

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

为避免扇出问题:
*始终将聚合应用于联接中最精细的表.
*除非两个表具有一对一的关系，否则不要对两个表中的字段应用聚合函数.
*通过不同的子查询分别获取汇总，然后合并结果.这可以在SQL语句中完成，也可以导出数据然后再执行.

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.

查询1:

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

查询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不要受到扇出的困扰.此时，您可以将它们导出并在php中处理结果.或者，您也可以在SQL中将它们组合在一起:

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

最后，SUM不使用关键字DISTINCT. DISTINCT仅对COUNT和GROUP_CONCAT聚合函数可用.以下是一段无效的SQL

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

这篇关于MySQL SUM()给出不正确的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！