SQL query how to divide two selects that return multiple columns

Question

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

It's very confusing having a table named *database* – Stu Nov 06 '22 at 00:14 — Stu, Nov 06 '22 at 00:14
it was just an example, changed to orders – mariana Nov 06 '22 at 00:19 — mariana, Nov 06 '22 at 00:19

score 1 · Accepted Answer · answered Nov 06 '22 at 00:31

1

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.

answered Nov 06 '22 at 00:31

ahmed

9,071
3
9
22

score 0 · Answer 2 · answered Nov 06 '22 at 00:28

0

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

answered Nov 06 '22 at 00:28

slambeth

887
5
17

SQL query how to divide two selects that return multiple columns

2 Answers2