CODEWITHSUNDEEP
mysqlsum
Philip Caputo
Philip Caputo

Reputation: 50

Java MySQL selecting multiple rows while summing one column

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

I have an Income table that holds all income wheather it be investment, business, paycheck or whatever. I want to pull the total of each type. I have reduced all possible income into 3 types, earned, portfolio, or passive. I want to get the sum(amount) for each in one resultset. The above code only selects the total for the earned type but I can not figure out how to get the other two types included. Thank you for any help.

Upvotes: 0

Views: 388

Answers (3)

inityk
inityk

Reputation: 486

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

This query would give you the sum of amount by each type (earned, portfolio, or passive)- If these are the 3 types in your income table

Try modifying this way

Upvotes: 2

Kaung Yee Hein
Kaung Yee Hein

Reputation: 46

You should place group by to end of statement.

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

Upvotes: 0

sagi
sagi

Reputation: 40481

This is called conditional aggregation :

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

Upvotes: 2

Related Questions