Question 113884 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query how to divide two selects that return multiple columns

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

Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search