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

Question

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

No images for text data, please. – Erwin Brandstetter Sep 21 '22 at 12:44 — Erwin Brandstetter, Sep 21 '22 at 12:44

Erwin Brandstetter · Accepted Answer · 2022-09-21T12:47:31.233

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;

Your assumptions are correct. I want all the commissions for a given airport and a given area. Actually I need more, the sum of all these commissions. But for simplicity I omitted that for now. The booking_date is of type `timestamp without time zone`. Unfortunately your query returns the right name for airport but `null` for the commission `com` — codyLine, Sep 20 '22 at 12:24
@codyLine That's because there are no rows for airport 2 and area 3 in your example. Also goes to show that the sum is actually simpler than the list. `timestamp` is the short name for `timestamp without time zone`, so my assimption is correct there, too. See: https://stackoverflow.com/a/9576170/939860 — Erwin Brandstetter, Sep 20 '22 at 13:02
OK, I rerun my query and I get indeed plenty of results but only when I remove the condition `b.areaid = 3`. Why ? — codyLine, Sep 20 '22 at 15:55
I tried also with `p` instead of `b`; `p.areaid = 3`. Same result — codyLine, Sep 20 '22 at 16:00
Oh, I just see your comment. No, there is plenty of bookings for area 3 and airport 2. No matter what area I choose, I get no results. — codyLine, Sep 20 '22 at 18:37
Plenty of bookings? In the given time frame? My query is solid. And my crystal ball is all out of juice. (`p.areaid = 3` is idempotent to `b.areaid = 3` in this query, btw.) — Erwin Brandstetter, Sep 20 '22 at 19:20
I meant my real data. I created a fresh test DB with the exact data like above. I run your code and please have a look at my screenshots in my edited question above. I also shared code to create the DB. — codyLine, Sep 21 '22 at 11:55
All I said holds. Consider my update. (Next time, please start the question with the fiddle.) — Erwin Brandstetter, Sep 21 '22 at 12:40

score 1 · Answer 2 · answered Sep 19 '22 at 23:07

1

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.

answered Sep 19 '22 at 23:07

klin

112,967
15
204
232

For me, the most important condition `ar.airport_id = 2` is missing in your statement. – codyLine Sep 20 '22 at 09:09
The query for `ar.airport_id = 2` returns no rows with not-null `com`, which results directly from the sample data. Of course, you can add any condition you want in the `WHERE` clause to the end of the query. – klin Sep 20 '22 at 09:17
Your query works only when I remove `p.areaid = 3` condition. Strange. – codyLine Sep 20 '22 at 12:37

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

2 Answers2