Passing the result of one SQL query to another

Question

I have a table in my DB (MariaDB 10.3) which shows the "rental" status of our product. Based on the percentage that is rented out, we need to retrieve a pricing formula.

I can get the percentage using the the following SQL statement the percentage is named as final_count

select (a.Rented / b.total) * 100 as final_count from
(Select
  Count(status)as Rented
From
  usfacilitymyob
where
status = 'rented' and zone="123mcs") a,
(Select
  Count(unitno)as total
From
  usfacilitymyob
where
  zone="123mcs") b

I would like to pass the result final count into the next SQL so we can get the appropriate discount structure. However the error I got is "final_count" is not a known column. Note (the IF statement is because I need to select the formulatype based on percentage and there are 6 different levels, so this will not be my final SQL statement)

SELECT * 
FROM 'usnewformula' where formulaType=IF(final_count>0.60,"sevenonesevenfive","sixone") in(
select (a.Rented / b.total) * 100 as final_count from
(Select
  Count(status)as Rented
From
  usfacilitymyob
where
status = 'rented' and zone="123mcs") a,
(Select
  Count(unitno)as total
From
  usfacilitymyob
where
  zone="123mcs") b)

What do I need to do to correct the SQL statement?

Passing the result of one SQL query to another

0 Answers0