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

Postgresql – Error "more than one row returned by a subquery used as an expression"

I have the following 3 tables:

airport

airport_id  name
1           Frankfurt
2           Paris
3           Amsterdam

area

areaid    name    airport_id
1         name1   2
2         name2   2
3         name3   3
4         name4   3

booking

id      booking_date        price   commission  areaid
1       2022-09-1T10:00     70      12          1
2       2022-09-2T11:00     60      16          2
3       2022-09-2T20:00     50      15          3
4       2022-09-3T01:00     110     15          3
5       2022-09-10T22:00    90      14          4
6       2022-09-11T19:00    65      12          1
7       2022-09-20T12:00    84      16          2

And I have this query 

SELECT ar.name,
     (SELECT (b.price * b.commission) AS com
        FROM booking AS b
        LEFT JOIN area AS p ON b.areaid = p.areaid
        AND p.areaid = 3
        AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')
      )
FROM airport AS ar WHERE ar.airport_id = 2

Running the query I get the error:

more than one row returned by a subquery used as an expression

I don’t understand what the problem is.

I added an SQL Fiddle to play with: http://sqlfiddle.com/#!17/8a09f/1

Tags:

2

Answers


  1. 0

    The query

    SELECT (b.price * b.commission) AS com
    FROM booking AS b
    LEFT JOIN area AS p ON b.areaid = p.areaid
    AND p.areaid = 3
    AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')

    returns 7 rows. A subquery used in the SELECT list must return no more than 1 row.

    A natural way to get what you want is to join all tables, e.g.:

    SELECT
    ar.name,
    (b.price * b.commission) AS com
FROM airport AS ar
LEFT JOIN area AS p 
    ON ar.airport_id = p.airport_id
LEFT JOIN booking AS b 
    ON b.areaid = p.areaid
    AND p.areaid = 3
    AND b.booking_date >= '2022-09-01T00:00' 
    AND b.booking_date <= '2022-09-30T23:59:59'

    Replace LEFT JOIN with JOIN to skip rows with null results.

    Login or Signup to reply.
  2. 0

    A subquery in the SELECT list is only allowed to return a single value. Not multiple rows, nor multiple columns. If you want any of those, the standard replacement is a LATERAL subquery. See:

    The more severe problem with your query is that it doesn’t make sense at all.

    This might be what you want, returning the list of all commissions (if any) for a given airport and a given area:

    SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
   SELECT b.price * b.commission / 100.0 AS com
   FROM   area    p
   JOIN   booking b USING (areaid)
   WHERE  p.airport_id = ar.airport_id  -- my assumption
   AND    p.areaid = 3
   AND    b.booking_date >= '2022-09-01'
   AND    b.booking_date <  '2022-10-01'
   ) b ON true
WHERE  ar.airport_id = 2;

    fiddle

    (But you get no results for com while asking for airport 2 and area 3, for which there are no matching entries.)

    Your subquery was uncorrelated. Assuming you really meant to link to the given airport via airport_id.

    LEFT JOIN area AS p made no sense in combination with the condition WHERE p.areaid = 3. That’s a hidden [INNER] JOIN. See:

    The filter b.booking_date <= '2022-09-30T23:59:59' may be slightly incorrect, too (unless your data is guaranteed to have a 1-minute resolution). Either way, b.booking_date < '2022-10-01' is the proper way to include "all of September". '2022-10-01' is a valid timestamp literal, short for '2022-10-01T00:00:00'.

    Assuming the value in commission is really meant to be a percentage. (But I removed the rounding I first had. You didn’t ask for that.)

    BTW, if you actually want the sum – a single value – a correlated (!) subquery becomes an option again:

    SELECT ar.name AS airport
     , (SELECT sum(b.price * b.commission) / 100.0
        FROM   area    p
        JOIN   booking b USING (areaid)
        WHERE  p.airport_id = ar.airport_id
        AND    p.areaid = 3
        AND    b.booking_date >= '2022-09-01'
        AND    b.booking_date <  '2022-10-01') AS com
FROM   airport ar
WHERE  ar.airport_id = 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search