Find Sum of Column with Certain String(s) in Another Column in a Table

Tags: php mysql

I am trying to work on a project that needs to find the total sum and the total sum with a certain criteria. Currently, I have managed to just calculate the total sum of column "price". Here is the code:

$sumQuery = "SELECT SUM(price) AS value_sum FROM tickets";
//$dbc is Database Connection
$sumResponse = @mysqli_query($dbc, $sumQuery);
$sumRow = mysqli_fetch_array($sumResponse); 
$sum = $sumRow['value_sum'];

I want to work upon this code and make it be more specific. Instead, I want it to ONLY calculate the sum of column "price" if in that row it has the string "Check" or "Cash" in the column "paytype". I have no idea where to start with this. I have done my research prior to asking this question, but came out with no results.

Here is a snippet of my table which may help you understand more:

Tickets Table

Here is a simple version of what I want to achieve if my description made no sense. Find sum of column "price" for each row with the string "Check" or "Cash" in the column "paytype".


Use conditional aggregation:

SELECT SUM(price) AS value_sum,
       SUM(CASE WHEN paytype IN ('Check', 'Cash') then price end) as check_or_cash
FROM tickets;

Of course, if you only want the sum for check/cash then you would use a WHERE clause.

This video can help you solving your question :)
By: admin