带有条件的PHP SQL SUM列

Question

I have table table_order like below :

orderid | orderdate   | price  | deptnr
--------+-------------+--------+----------
1       | 2019-07-12  | 50000  | 1
2       | 2019-07-12  | 30000  | 1
3       | 2019-07-13  | 40000  | 1
4       | 2019-07-14  | 50000  | 1

I need to output all column on table_order with condition if there are same date, the price value will accumulate into one record (record on column 1 and 2 must be merge with total price 50000+30000). Here what i've done:

// $sql = mysqli_query($con, "SELECT * FROM table_order
//  WHERE deptnr='$departmetnnr' AND orderstatus='CLOSE'
//  AND orderdate BETWEEN '$oneyearbefore' AND '$currentdate' ORDER BY orderdate"); 

$sql = mysqli_query($con, "
    SELECT SUM(price) AS totalprice 
      FROM table_order 
     WHERE deptnr = '$departmetnnr' 
       AND orderstatus='CLOSE' 
       AND orderdate BETWEEN '$oneyearbefore' AND '$currentdate' 
     ORDER
        BY orderdate
");

$data = array();
while($row=mysqli_fetch_assoc($sql)) {
    $data[] = ($row);
}               
$json = json_encode($data);

Honestly I'm new in PHP and SQL, I try to build simple web service for Mobile Apps, so I want to process all calculation in server and output as JSON.

I have read some basic statement about PHP and SQL (in w3school, sometime in Stack Overflow answer), problem regarding with mine like this, but I not yet find the right output. I love to read any suggestion / reference link if available. Thanks in advance.

Answer 1

You need to GROUP BY orderdate if you want to get the sum for each date. This creates one group for each of the distinct values of orderdate, and you can then use the aggregate function SUM() to get each sum for each group (so for you, that's the sum of price for each date).

If you do not supply a GROUP BY orderdate, you just get the sum across all rows.

SELECT SUM(price) as totalprice, orderdate
FROM table_order
WHERE deptnr='$departmetnnr' 
  AND orderstatus='CLOSE' 
  AND orderdate  BETWEEN '$oneyearbefore' AND '$currentdate' 
GROUP BY orderdate
ORDER BY orderdate

That said, you are currently injecting variables directly into the query, which should be avoided by using a prepared statement.

$stmt = $con->prepare("SELECT SUM(price) as totalprice, orderdate
                       FROM table_order
                       WHERE deptnr=? 
                         AND orderstatus='CLOSE' 
                         AND orderdate  BETWEEN ? AND ? 
                       GROUP BY orderdate
                       ORDER BY orderdate");
$stmt->bind_param("sss", $departmetnnr, $oneyearbefore, $currentdate);
$stmt->execute();
$stmt->bind_result($totalprice, $orderdate);
while ($stmt->fetch()) {
    $data[] = ["totalprice" => $totalprice, "orderdate" => $orderdate];
}
$stmt->close();
$json = json_encode($data);

You can also use SQL functions to create 1 year in the past and get the current date, instead of using PHP values. Use CURDATE() to get the current date, then define an interval of 1 year which you subtract from, these will then become the ranges for your BETWEEN.

$stmt = $con->prepare("SELECT SUM(price) as totalprice, orderdate
                       FROM table_order
                       WHERE deptnr=? 
                         AND orderstatus='CLOSE' 
                         AND orderdate  BETWEEN CURDATE() AND CURDATE() - INTERVAL 1 YEAR
                       GROUP BY orderdate
                       ORDER BY orderdate");
$stmt->bind_param("s", $departmetnnr);
$stmt->execute();
$stmt->bind_result($totalprice, $orderdate);
while ($stmt->fetch()) {
    $data[] = ["totalprice" => $totalprice, "orderdate" => $orderdate];
}
$stmt->close();
$json = json_encode($data);