CODEWITHSUNDEEP
mysqlsql
Said
Said

Reputation: 186

How to get the total of 3 columns in MySQL

I'm trying to get the total of of 3 columns in this MySQL Select Query, I getting the results correctly, I have no idea how to obtain the sum(Party1 + Party2 + Party3)

I've tried to separate each into its own select, no result.

Thanks

SELECT
sum(`InvoiceItem`.`qty`) AS `Quantity`,
IF(sum(qty) < 4999, sum(qty) * 2500, sum(qty) * 5000) AS party1,
IF(sum(qty) < 4999, sum(qty) * 2000, sum(qty) * 4000) AS party2,
IF(sum(qty) < 4999, sum(qty) * 500, sum(qty) * 1000) AS party3
     FROM `InvoiceItem`
 LEFT JOIN `Invoice` ON `InvoiceItem`.`parent` = `Invoice`.`name`
 LEFT JOIN `Ticket` ON `Invoice`.`wbt` = `Ticket`.`id`
 WHERE 
`InvoiceItem`.`status` = 1
 AND 
 MONTH(posting_date) = MONTH(CURDATE()) -2

 //Result of Query above
---------------------------------------------
 Quantity | party1 | party2 | Party3 | Total = (Party1+Party2+Party3)
--------------------------------------------- 
 5000     | 1000   | 4000   | 5000   | ??

Upvotes: 1

Views: 180

Answers (1)

P3trur0
P3trur0

Reputation: 3225

This should work:

SELECT sum(res.Quantity + res.party1 + res.party2 + res.party3) as total, 
res.Quantity,
res.party1, 
res.party2, 
res.party3 
FROM (
SELECT
sum(`InvoiceItem`.`qty`) AS `Quantity`,
IF(sum(qty) < 4999, sum(qty) * 2500, sum(qty) * 5000) AS party1,
IF(sum(qty) < 4999, sum(qty) * 2000, sum(qty) * 4000) AS party2,
IF(sum(qty) < 4999, sum(qty) * 500, sum(qty) * 1000) AS party3
     FROM `InvoiceItem`
 LEFT JOIN `Invoice` ON `InvoiceItem`.`parent` = `Invoice`.`name`
 LEFT JOIN `Ticket` ON `Invoice`.`wbt` = `Ticket`.`id`
 WHERE 
`InvoiceItem`.`status` = 1
 AND 
 MONTH(posting_date) = MONTH(CURDATE()) -2
 ) as res;

Upvotes: 3

Related Questions