mysql - mysql中如何求最大值的和？

Question

    SELECT 
       DISTINCT(ecode_id)
      ,MAX(eligible_allowance)AS monthlyBudgetAmt
      ,approval_date
    FROM vefm_dailybudget
    WHERE
        division=1 AND region=1
        AND unit=1 AND common_status IN('Finance - II Approved','Cash Disbursement')
        AND eligible_allowance != 0
        AND approval_date BETWEEN ' 2019-02-01'
        AND '2019-02-28' AND budget_type='monthly'
    GROUP BY ecode_id ORDER BY ecode_id

下面是我从上面的查询总共 91 行中得到的值。我想显示合格津贴的总和，而不是 91 行。有人可以帮忙吗

Ecode ID Eligible Allowance Approval Date
E2404   12000   2019-02-14
E2660   9000    2019-02-19
E2694   10500   2019-02-14
E2739   7500    2019-02-14
E2911   7500    2019-02-14
E2912   7500    2019-02-14
E2929   7500    2019-02-14
E2967   7500    2019-02-14
E3013   7500    2019-02-14
E3100   6000    2019-02-14
E3168   7500    2019-02-14
E3199   7500    2019-02-14
E3225   7500    2019-02-27
E3301   6000    2019-02-14
E3441   6000    2019-02-14
E3451   6000    2019-02-14
E3548   6000    2019-02-22
E3581   6000    2019-02-14
E3602   6000    2019-02-22
E3647   7500    2019-02-02
E3650   6000    2019-02-14
E3684   6000    2019-02-14
E3698   7500    2019-02-14
E3701   6000    2019-02-27
E3703   7500    2019-02-14

我需要上述查询的总和值为 516000

Answer 1

您需要对您的查询进行 monthlyBudgetAmt 求和:

SELECT SUM(t.monthlyBudgetAmt) AS total FROM (
    SELECT 
      MAX(eligible_allowance) AS monthlyBudgetAmt
    FROM vefm_dailybudget
    WHERE
        division=1 AND region=1
        AND unit=1 AND common_status IN('Finance - II Approved','Cash Disbursement')
        AND eligible_allowance != 0
        AND approval_date BETWEEN '2019-02-01'
        AND '2019-02-28' AND budget_type='monthly'
    GROUP BY ecode_id
) t