Lewis Smith Lewis Smith - 7 months ago 8
SQL Question

I am trying to divide two select queries using sql, not sure if this is possible or not

Below is my query, I think in principle it should work, but am not sure if it is indeed possible or I am thinking too outside the box for this one.

SELECT
(SELECT `orders`.`Status`, COUNT(*) AS COUNT_2 FROM `orders` `sw_orders` WHERE STATUS = 'booking' AND Date(OrderDate) <= CURDATE() AND Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)) /
(SELECT `orders`.`Status`, COUNT(*) AS COUNT_2 FROM `orders` `sw_orders` WHERE STATUS = 'quote' AND Date(OrderDate) <= CURDATE() AND Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY))
AS result


That should return the value of 2 results where bookings is divided by quotes

Answer
SELECT count(case when STATUS = 'booking' then 1 end) /
           count(case when STATUS = 'quote' then 1 end)
FROM `sw_orders`
WHERE Date(OrderDate) <= CURDATE()
  AND Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)