Query that counts values in column of MySQL database

Question

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?

score 2 · Accepted Answer · answered Sep 15 '16 at 22:18

2

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;

answered Sep 15 '16 at 22:18

Gordon Linoff

1,242,037
58
646
786

Thank you. That works exactly the way I'd like it to. To get the total number of all `Status` values, would you just run a `SUM(num_NewEstimate, num_Approved, ...)`? Is that the best way to accomplish that piece of the puzzle? – Liz Sep 15 '16 at 22:26
1

@LizBanach: MySQL won't allow you to reference a column alias within the SELECT list of the same query. You could make this query an inline view (with the requisiste performance penalty), and reference the column names in an outer query. Or, either 1) repeat the same expressions (that get the individual counts) but combine them with an addition operator (+) instead of the comma separator. or 2) do another expression that tests for all of the values `SUM(status IN ('New Estimate','Approved',...)) AS num_in_status_list`. – spencer7593 Sep 15 '16 at 22:31
Thank you for the guidance @spencer7593 – Liz Sep 15 '16 at 22:35
1

I'm not real wild about the `EXTRACT(YEAR_MONTH FROM CreatedDate) >=` condition. From a performance standpoint, we'd prefer the see the query use a comparison to the bare column, potentially allowing MySQL to make effective use of a suitable index. Something of the form... **`CreatedDate >= `** with the comparison to an expression that returns a DATE value, something like **`DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -1 YEAR`**. – spencer7593 Sep 15 '16 at 22:41
@spencer7593 - I am new to MySQL so that advice is very much appreciated. I was trying to get something similar to work but was unable to, and ended up with the query above. However, `CreatedDate >= DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -1 YEAR` yielded accurate results for me. Thank you very much. – Liz Sep 15 '16 at 22:46
1

I'm glad you found the comment useful, even if it wasn't directly related to your question. The important thing to note is that if `CreatedDate` is wrapped in a function (or expression) in the predicate (i.e. condition in the WHERE clause), then MySQL will be forced to evaluate that function or expression for *every* row in the table, and won't be able to use a range scan operation on an index. MySQL can use an index to quickly eliminate vast swaths of rows that it knows cannot possibly satisfy the condition, without having to examine all of the values. – spencer7593 Sep 15 '16 at 22:51

Query that counts values in column of MySQL database

1 Answers1