CODEWITHSUNDEEP
mysqlsql
charlie
charlie

Reputation: 481

SQL SUM if field is a specific value

I have an SQL Query that used to have inclusive <> '1' as a clause but I had to remove it to ensure I pick up all the data but I still need to count the value of the inclusive field in the SUM

Is it possible to make the SUM(customer_cost) as customer_total not include rows where inclusive <> '1'

$callChargesSql = "SELECT 
                customer,
                source,
                source_name,
                calltype,
                SUM(customer_cost) as customer_total,
                SUM(cost) as cost,
                SUM(recording_cost) as recording_cost,
                SUM(recording_customer) as recording_customer
            FROM
                billing_calldata
            WHERE
                (
                    customer = '".db_string($result["sequence"])."' OR
                    customer IN 
                    (
                        SELECT 
                            sequence 
                        FROM 
                            customer 
                        WHERE 
                            resellerid = '".db_string($result["sequence"])."'
                    )
                ) AND
                (
                    (
                        MONTH(timestamp) = '".db_string($calls["month"])."' AND
                        YEAR(timestamp) = '".db_string($calls["year"])."'
                    ) OR
                    status = 'y'
                )
            GROUP BY customer, source, calltype
            ORDER BY customer, timestamp ASC;";

Upvotes: 0

Views: 411

Answers (2)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

In MySQL you can use IF statement within SUM operator like:

SUM(IF(inclusive = '1',customer_cost, 0)) as customer_total

Below simplified example:

select 
    customer_id,
    sum(customer_cost) as total_customer_cost,
    sum(if(inclusive=1, customer_cost, 0)) as inclusive_customer_cost
from billing_calldata
group by customer_id
;

Here you can test it: SQLize.online

In my test you can

Upvotes: 2

lakta
lakta

Reputation: 278

You can use something like this: SUM(case when inclusive = '1' then customer_cost else 0 end) as customer_total

In your example query:

$callChargesSql = "SELECT 
                customer,
                source,
                source_name,
                calltype,
                SUM(case when inclusive = '1' then customer_cost else 0 end) as customer_total,
                SUM(cost) as cost,
                SUM(recording_cost) as recording_cost,
                SUM(recording_customer) as recording_customer
            FROM
                billing_calldata
            WHERE
                (
                    customer = '".db_string($result["sequence"])."' OR
                    customer IN 
                    (
                        SELECT 
                            sequence 
                        FROM 
                            customer 
                        WHERE 
                            resellerid = '".db_string($result["sequence"])."'
                    )
                ) AND
                (
                    (
                        MONTH(timestamp) = '".db_string($calls["month"])."' AND
                        YEAR(timestamp) = '".db_string($calls["year"])."'
                    ) OR
                    status = 'y'
                )
            GROUP BY customer, source, calltype
            ORDER BY customer, timestamp ASC;";

Upvotes: 1

Related Questions