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

Answer

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:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

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:

• SQL / PostgreSQL left join ignores “on = constant” predicate, on left table

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;