mysql SUM 金额 IF 引用不同 GROUP By 类别

Question

我正在尝试按类别对金额进行求和，但存在基于引用编号的重复金额，并且我只想为每个引用包含 1 个金额。大约有100K个不同的引用号，全线有4个差异量。

我正在分析的数据如下所示:

reference | category | amount | status 
5574682   | cat1     | 45     | active 
5574682   | cat1     | 45     | inactive 
5574684   | cat1     | 95     | active 
5574869   | cat2     | 65     | active 
5574869   | cat2     | 65     | inactive 
5574870   | cat2     | 55     | active 
5574870   | cat2     | 55     | inactive 
5574891   | cat3     | 95     | active 
5574892   | cat3     | 45     | active 
5574892   | cat3     | 45     | inactive

下面显示了作为选择的正确结果，但不是按类别的总和

SELECT
    a.reference,
    c.category,
    a.amount
FROM
    table1_ref a
    JOIN (
        SELECT *
        FROM
            table_ref a
            JOIN table_requests b ON a.transactionid = b.requestid
            JOIN table_users c ON a.user_code = c.user_code
        WHERE b.filename IN ('20190614','20190625','20190628')
    ) b ON a.reference = b.reference
    JOIN table_users c ON a.user_code = c.user_code
WHERE
    a.date BETWEEN '2019-08-01' AND '2019-08-31'
    AND c.category IN (cat1, cat2, cat3)
GROUP BY
    a.reference,
    c.category;

使用上面的代码，我得到的结果如下所示:

reference | category | amount
5574682   | cat1     | 45
5574684   | cat1     | 95
5574869   | cat2      | 65
5574870   | cat2      | 55
5574891   | cat3      | 95
5574892   | cat3      | 45

我的预期结果如下

cat1 | 140
cat2 | 120
cat3 | 140

Answer 1

更新:

如果您需要获得这样的结果:

reference | category | amount | status 
----------|----------|--------|---------
5574682   | cat1     | 45     | active 
5574682   | cat1     | 45     | inactive 
5574684   | cat1     | 95     | active
5574869   | cat2     | 65     | inactive -- Lines below
5574869   | cat2     | 65     | inactive -- would be impossible to get
5574870   | cat2     | 55     | inactive -- with GROUP BY, because
5574870   | cat2     | 55     | inactive -- `reference`, `category` and `status`
5574891   | cat3     | 95     | inactive -- are the same among pairs
5574892   | cat3     | 45     | inactive -- so they would be represented as one row
5574892   | cat3     | 45     | inactive -- with total amount

然后您必须使用聚合 SUM() 函数并在外部列中列出一个附加列，如下所示:

SELECT
    a.reference,
    c.category,
    SUM(a.amount) as amount,    -- CHANGED
    SOMETABLE.status            -- ADDED
FROM
    table1_ref a
    JOIN (
        SELECT *
        FROM
            table_ref a
            JOIN table_requests b ON a.transactionid = b.requestid
            JOIN table_users c ON a.user_code = c.user_code
        WHERE b.filename IN ('20190614','20190625','20190628')
    ) b ON a.reference = b.reference
    JOIN table_users c ON a.user_code = c.user_code
WHERE
    a.date BETWEEN '2019-08-01' AND '2019-08-31'
    AND c.category IN (cat1, cat2, cat3)
GROUP BY
    a.reference,
    c.category,
    SOMETABLE.status;             -- ADDED