SQL query how to divide two selects that return multiple columns-CodePudding

I have the following table :

city	state	numOrder	date	deadlineDate
NY	NY	111	2022/11/05	2022/11/06
LA	CA	222	2022/11/01	2022/10/01
SD	CA	333	2022/05/05	2022/11/06
LA	CA	444	2022/11/01	2022/05/01

I need to calculate the number of orders placed before the deadline divided by the number of orders placed by each state and city:

(SELECT state, city ,count(*) 
FROM orders
WHERE date <= deadlineDate
group by state, city) /
(SELECT state, city ,count(*) 
FROM orders
group by state, city)

I tried:

SELECT (
         SELECT state, city ,count(*) 
         FROM orders 
         WHERE serviceDate <= limitDate 
         group by state, city
       )/
       (
         SELECT state, city ,count(*) 
         FROM orders 
         group by state, city
       )
FROM orders

But the I got ERROR:

Subquery must return only one column

CodePudding user response：

Try the following:

SELECT state, city, 
       COUNT(*) FILTER (WHERE date <= deadlineDate)*1.0 / COUNT(*) AS result
FROM orders
GROUP BY state, city

See a demo.

CodePudding user response：

Join the tables as two subquery tables and do the math in the select

SELECT A.COL1/B.COL1 AS MY_RATIO_COL
FROM
  (SELECT COL1 FROM MY_TABLE WHERE [BLA BLA BLA]) A
  JOIN
  (SELECT COL1 FROM MY_TABLE WHERE [yata yata]) B
  ON A.KEYCOL1 = B.KEYCOL1