Liz Banach Liz Banach - 2 months ago 8
MySQL Question

Query that counts values in column of MySQL database

I have a MySQL query that adds up all of the

ClientCostToDate
rows (type is
DECIMAL
) in my database by month and returns the data as JSON.

My PHP script is:

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


Here is the 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"
}
]


There is another column in MySQL database
Status
(
VARCHAR
type). It is comprised of the following values: New Estimate, Approved, Invoiced, Awaiting Billing, Cancelled, With Client, Cost Submitted.

I need to write a MySQL query that gives me all of the statuses for the rows that make up
SUM(ClientCostToDate) AS ClientCostsTotal
. I then need to count the number of each type of status (New Estimate, Approved, Invoiced, Awaiting Billing, Cancelled, With Client, Cost Submitted) per month. What is the best way to accomplish this?

Answer

You can put each value in a separate column, using conditional aggregation:

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;