CODEWITHSUNDEEP
mysqlsql
AxOn
AxOn

Reputation: 101

how to calc difference between two column with results

Could i calc for each row column Profit ?

SELECT
SUM(CASE WHEN m.billable = 0 THEN r.rate ELSE 0 END) AS Revenue, -- 33 + 34 + 456 + 52...etc = 5500 
    SUM(CASE WHEN m.billable = 1 AND m.optimized = 0 THEN r.rate ELSE 0 END) AS Costs,-- 33 + 4...etc = 339 
    5500 - 339 AS Profit -- I need to get this difference 
    FROM messages AS m
JOIN rates AS r ON (r.id = m.rate_id    )
    GROUP BY
            m.client_account_id,
            m.mcc,
            m.mnc

enter image description here

enter image description here

I want to get

| Revenue | Costs | Profit
   5500      500     5000

Upvotes: 0

Views: 34

Answers (2)

Barmar
Barmar

Reputation: 780724

Move your query into a subquery, then subtract the results.

SELECT Revenue, Costs, Revenue - Costs AS Profit
FROM (
    SELECT
        SUM(CASE WHEN m.billable = 0 THEN r.rate ELSE 0 END) AS Revenue,
        SUM(CASE WHEN m.billable = 1 AND m.optimized = 0 THEN r.rate ELSE 0 END) AS Costs
    FROM YourTable) AS x

Joining your two tables, and showing the results grouped by account, it would be:

SELECT client_account_id, mmc, mnc, Revenue, Costs, Revenue - Costs AS Profit
FROM (
    SELECT
        m.client_account_id, m.mmc, m.mnc
        SUM(CASE WHEN m.billable = 0 THEN r.rate ELSE 0 END) AS Revenue,
        SUM(CASE WHEN m.billable = 1 AND m.optimized = 0 THEN r.rate ELSE 0 END) AS Costs
    FROM messages AS m
    JOIN rates AS r ON r.id = m.rate_id
    GROUP BY m.client_account_id, m.mmc, m.mnc
) AS x

Upvotes: 2

Johan
Johan

Reputation: 929

Simply put it in a sub query :

SELECT Revenue - Costs as Profit
FROM (
SELECT
SUM(CASE WHEN m.billable = 0 THEN r.rate ELSE 0 END) AS Revenue,  
SUM(CASE WHEN m.billable = 1 AND m.optimized = 0 THEN r.rate ELSE 0 END) AS Costs
) as temp

Upvotes: 2

Related Questions