Skip to content

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

I have the following 3 tables:


airport_id  name
1           Frankfurt
2           Paris
3           Amsterdam


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


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 (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:!17/8a09f/1



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 AS airport,
FROM   airport ar
   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;


(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 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;
User contributions licensed under: CC BY-SA
7 People found this is helpful