php - 查询对MySQL数据库的列中的值进行计数的查询

This question already has answers here:

need to return two sets of data with two different where clauses
（2个答案）
三年前关闭。
我有一个MySQL查询，它按月累加数据库中的所有ClientCostToDate行（类型为DECIMAL），并将数据作为JSON返回。
我的PHP脚本是：

//the sql query to be executed
$estimates_query = "SELECT DATE_FORMAT(CreatedDate, '%M %Y') AS CreatedMonth,
 SUM(ClientCostToDate) AS ClientCostsTotal,
 EXTRACT(YEAR_MONTH FROM CreatedDate) AS CreatedYearMonth
 FROM Estimates
 WHERE CreatedDate IS NOT NULL
 AND EXTRACT(YEAR_MONTH FROM CreatedDate) >= EXTRACT(YEAR_MONTH FROM CURDATE())-100
 GROUP BY DATE_FORMAT(CreatedDate, '%M')
 ORDER BY CreatedYearMonth";

//storing the result of the executed query
$result = $conn->query($estimates_query);

//initialize the array to store the processed data
$estimatesJsonArray = array();

//check if there is any data returned by the sql query
if ($result->num_rows > 0) {
//converting the results into an associative array
while ($row = $result->fetch_assoc()) {
$jsonArrayItem = array();
$jsonArrayItem['date'] = $row['CreatedMonth'];
$jsonArrayItem['clientCostsTotal'] = $row['ClientCostsTotal'];
//append the above created object into the main array
array_push($estimatesJsonArray, $jsonArrayItem);
}
}

//close the connection to the database
$conn->close();

//set the response content type as json
header('Content-type: application/json');
//output the return value of json encode using the echo function
echo json_encode($estimatesJsonArray, JSON_PRETTY_PRINT);

以下是JSON：

[
{
    "date": "February 2016",
    "clientCostsTotal": "21211.25"
},
{
    "date": "March 2016",
    "clientCostsTotal": "206996.25"
},
{
    "date": "April 2016",
    "clientCostsTotal": "74667.50"
},
{
    "date": "May 2016",
    "clientCostsTotal": "61128.75"
},
{
    "date": "June 2016",
    "clientCostsTotal": "267740.50"
},
{
    "date": "July 2016",
    "clientCostsTotal": "200946.75"
},
{
    "date": "August 2016",
    "clientCostsTotal": "0.00"
}
]

MySQL数据库中还有一列Status（VARCHAR类型）。它由以下值组成：新估算值、已批准值、已开具发票值、正在等待帐单值、已取消值、已向客户提交的值、已提交的成本值。
我需要编写一个MySQL查询，它给出组成SUM(ClientCostToDate) AS ClientCostsTotal的行的所有状态。然后我需要计算每月每种状态的数量（新估计、已批准、已开具发票、等待帐单、已取消、客户已提交、成本已提交）。实现这一目标的最佳方法是什么？

最佳答案

可以使用条件聚合将每个值放在单独的列中：

SELECT DATE_FORMAT(CreatedDate, '%M %Y') AS CreatedMonth,
       SUM(ClientCostToDate) AS ClientCostsTotal,
       SUM(status = 'New Estimate') as num_NewEstimate,
       SUM(status = 'Approved') as num_Approved,
       . . .
FROM Estimates
WHERE CreatedDate IS NOT NULL AND
      EXTRACT(YEAR_MONTH FROM CreatedDate) >= EXTRACT(YEAR_MONTH FROM CURDATE())-100
GROUP BY DATE_FORMAT(CreatedDate, '%M %Y')
ORDER BY CreatedYearMonth;

关于php - 查询对MySQL数据库的列中的值进行计数的查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39521093/