Matthew Moran Matthew Moran - 1 year ago 78
MySQL Question

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

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".

Thank you all so much who can help me figure this out.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download