Java MySQL在对一列求和时选择多行

Question

String sql = "SELECT TYPE, SOURCE, SUM(AMOUNT) AS AMOUNT FROM INCOME WHERE TYPE = 'EARNED' " + 
                    "AND DATE BETWEEN '" + year + "-" + month + "-" + "01' AND '" + year + "-" + month + "-" + day + "'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

我有一个收入表，里面有所有的收入，无论是投资，商业，薪水还是其他。我想要拉取每种类型的总和。我已经将所有可能的收入减少到3种类型，赚取，投资组合，或被动。我想在一个结果集中得到每个结果集的和(数量)。上面的代码只选择了赚来的类型的总数，但我不知道如何将其他两种类型包括在内。谢谢你的帮助。

Answer 1

String sql1 = "SELECT TYPE, SUM(AMOUNT) AS AMOUNT " + 
               "FROM INCOME " +  
               "WHERE DATE BETWEEN '" + year + "-" + month + "-" + "01' " + 
               "AND '" + year + "-" + month + "-" + day + "'"+
               "GROUP BY TYPE " ;
stmt = conn.createStatement();
rs = stmt.executeQuery(sql1);

此查询将为您提供每种类型(收入、投资组合或被动)的金额总和-如果这些类型是收入表中的3种类型

尝试以这种方式修改

Answer 2

这称为条件聚合：

SELECT 
        SUM(CASE WHEN TYPE = 'EARNED' THEN AMOUNT ELSE 0 END) AS EARNED_cnt,
        SUM(CASE WHEN TYPE = 'PASSIVE' THEN AMOUNT ELSE 0 END) AS PASSIVE_cnt,
        SUM(CASE WHEN TYPE = 'PORFOLIO' THEN AMOUNT ELSE 0 END) AS PORFOLIO_cnt
FROM INCOME

Answer 3

您应该将group by置于语句的末尾。

String sql = "SELECT TYPE, SOURCE, SUM(AMOUNT) AS AMOUNT
    FROM INCOME WHERE DATE BETWEEN '" +
    year + "-" + month + "-" + "01' AND '" +
    year + "-" + month + "-" + day + "' GROUP BY TYPE"; 
stmt = conn.createStatement(); 
rs = stmt.executeQuery(sql);