CODEWITHSUNDEEP
mysqlsql
Lewis Smith
Lewis Smith

Reputation: 1345

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

Upvotes: 0

Views: 52

Answers (3)

jarlh
jarlh

Reputation: 44746

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)

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

SELECT 
   SUM(CASE WHEN STATUS = 'booking' THEN 1 ELSE 0 END) /
   SUM(CASE WHEN STATUS = 'quote' THEN 1 ELSE 0 END)
FROM `sw_orders` 
WHERE Date(OrderDate) <= CURDATE() 
  AND Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)

I suppose COUNT of STATUS = 'quote' is not 0

Upvotes: 0

Priyanshu
Priyanshu

Reputation: 881

select count(status ='booking' or null) / count(status = 'quote') as result from table_name where  Date(OrderDate) <= CURDATE() AND Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)

Please watch out for syntax error. I have not taken care of.

Upvotes: 0

Related Questions