CODEWITHSUNDEEP
mysql
Thomas Williams
Thomas Williams

Reputation: 1548

How to use an alias in a calculation in mysql, without multiple rows

I want to use an alias in a calculation, but I do know that this is not the correct way. Ok so my original code was this

SELECT ROUND(SUM((`Unit_Cost`*`Quantity`)*`ExchangeRate`),2) as Cost,
 ROUND(SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`/`salesinvoice_products`.`VAT`,2) as `VATValue`
FROM `salesinvoice`
 LEFT JOIN `customers` 
   ON `salesinvoice`.`customer_id` = `customers`.`customer_id`
 LEFT JOIN `salesinvoice_products` 
  ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
WHERE `PaymentTerms` > 1 
GROUP BY `salesinvoice`.`SalesInvoice_id` 
ORDER BY `DateEntered` DESC

But I want to add the VATValue to the Cost. Obviously if I just do +VATValue it would complain that VATValue doesn't exist, but that is the way it reads aliases. So next I tried this with a subquery thinking that was the answer. So here is my code with a subquery

SELECT ROUND(SUM((`Unit_Cost`*`Quantity`)*`ExchangeRate`),2) as Cost,
  (
    SELECT ROUND(SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`/`salesinvoice_products`.`VAT`,2)
    FROM `salesinvoice`
  ) as tVAT
 FROM `salesinvoice`
 LEFT JOIN `customers` 
   ON `salesinvoice`.`customer_id` = `customers`.`customer_id` 
 LEFT JOIN `salesinvoice_products` 
   ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
WHERE `PaymentTerms` > 1 
GROUP BY `salesinvoice`.`SalesInvoice_id` 
ORDER BY `DateEntered` DESC

Unfortunately this time it gives an error #1242 - Subquery returns more than 1 row. I know I haven't added the alias on first, but first I am just making sure the subselect works, and it obviously doesn't.

I believe I may need a where clause in the subquery, but I am not sure.

Can anyone tell me what I am doing wrong?

Upvotes: 0

Views: 35

Answers (1)

user1898027
user1898027

Reputation: 340

Instead of that subquery I would do the following:

SELECT `Cost`, `VATValue`, (`VATValue`+`Cost`) as CostAndVAT 
FROM
( SELECT ROUND(SUM((`Unit_Cost`*`Quantity`)*`ExchangeRate`),2) as Cost,
ROUND(SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`/`salesinvoice_products`.`VAT`,2) as `VATValue`
FROM `salesinvoice`
LEFT JOIN `customers` 
ON `salesinvoice`.`customer_id` = `customers`.`customer_id`
LEFT JOIN `salesinvoice_products` 
ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
WHERE `PaymentTerms` > 1 
GROUP BY `salesinvoice`.`SalesInvoice_id` 
ORDER BY `DateEntered` DESC) a

Upvotes: 1

Related Questions