如何在一列中获取所有值mysql sum和subtract

Question

I created an API for an accounting application. I have income and spending in one column. How can I sum all the income and spending and subtract?

This my code:

private function list_recap() {

    if($this->get_request_method() != "GET") {
        $this->response('', 406);
    }

    $year  = $this->_request['year'];
    $month = $this->_request['month'];

    $sqlPlus = $this->db->query("SELECT SUM(nominal) 
            FROM lap_keuangan 
                WHERE YEAR(tanggal) = '$year' 
                AND MONTH(tanggal) = '$month' 
           AND kategori = 1");

    $sqlMin  = $this->db->query("SELECT SUM(nominal) 
            FROM lap_keuangan 
                WHERE YEAR(tanggal) = '$year' 
                AND MONTH(tanggal) = '$month' 
                AND kategori = -1");


    $result = array();
    while($rowPlus = $sqlPlus->fetch(PDO::FETCH_ASSOC)) {
        $plus = $result[] = $rowPlus;
    }

    while($rowMin = $sqlMin->fetch(PDO::FETCH_ASSOC)) {
        $minus = $result[] = $rowMin;
    }

    $this->response($this->json($result), 200);
}

Answer 1

You can use CASE-THEN-ELSE construction, try to execute this SQL statement:

SELECT
    SUM(CASE WHEN kategori = 1 THEN nominal ELSE 0 END) -
    SUM(CASE WHEN kategori = -1 THEN nominal ELSE 0 END)
FROM lap_keuangan
WHERE
    YEAR(tanggal) = '$year'
    AND MONTH(tanggal) = '$month'
    AND kategori = 1

PS:

Your code is vulnerable for SQL Injection attacks. You MUST NOT pass SQL query parameters using strings concatenation. Use PDO instead and bind arguments.