CODEWITHSUNDEEP
phpmysql
Abysinian
Abysinian

Reputation: 55

PHP/MySQL - Calculate Profit by Adding Up Sales and Purchases then Subtracting

I'm working on a PHP application with a MySQL database which stores a list of transactions which have been made by a company.

These sales and purchases are held in the same table with an identifier column with either a p or s in it.

I'm looking to calculate the profit by taking all the transactions where salesOrPurchase = 's' and adding them all up, then doing the same for salesOrPurchase = 'p', then subtracting our purchases from our sales to work out the profit.

Something along the lines of:

SELECT
SUM(amount) WHERE salesOrPurchase = 's' as SalesTotal,
SUM(amount) WHERE salesOrPurchase = 'p' as PurchasesTotal,
(SalesTotal - PurchasesTotal) as Profit
FROM transactions

Upvotes: 1

Views: 1516

Answers (2)

Jonny Henly
Jonny Henly

Reputation: 4213

How about a subquery?

SELECT SalesTotal, PurchasesTotal, (SalesTotal-PurchasesTotal) as Profit
FROM (SELECT (SELECT SUM(amount) FROM transactions WHERE salesOrPurchase = 's') as SalesTotal,
             (SELECT SUM(amount) FROM transactions WHERE salesOrPurchase = 'p') as PurchasesTotal
     ) t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can't use alias in the same select where they are defined. Instead, use conditional aggregation for all three:

SELECT SUM(case when salesOrPurchase = 's' then amount end) as SalesTotal,
       SUM(case when salesOrPurchase = 'p' then amount end) as PurchasesTotal,
       SUM(case when salesOrPurchase = 's' then amount
                when salesOrPurchase = 'p' then - amount
           end)  as Profit
FROM transactions;

Upvotes: 0

Related Questions