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

Postgresql – How to make this subquery return more than one row?

I select data from temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS "tmp" 
(
    "fuel_type" TEXT,
    "litres" NUMERIC(8,2),
    "is_rent" BOOLEAN
);

insert into tmp values ('petrol', 10, False);
insert into tmp values ('petrol', 20, False);
insert into tmp values ('diesel', 20, False);

Like this:

SELECT
    (SELECT row(fuel_type, SUM(litres)) 
     FROM tmp 
     GROUP BY fuel_type);

In case there more than one fuel_type in tmp, I get this error:

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

I expect this output

ARRAY_AGG
fuel_type|litres
petrol,40
diesel,20
Tags:

2

Answers


  1. 0

    The way your query was constructed was the issue. Try this below code instead to get the expected output.

    WITH FuelSums AS (
    SELECT 
        fuel_type, 
        SUM(litres) AS total_litres
    FROM 
        tmp2
    GROUP BY 
        fuel_type
)
SELECT 
    STRING_AGG(CONCAT(fuel_type, ',', total_litres), '; ') AS fuel_data
FROM 
    FuelSums;
    Login or Signup to reply.
  2. 0

    It is not clear what realy is your expected result. Select comand results with rows and columns when data was found. From your expected result I could not (without reasonable doubt) conclude was it one or two rows nor one or two columns (putting aside unexplained ARRAY_AGG above it).

    1. If you want 2 rows of data in 1 column named "fuel_type | litres":
    Select    Concat( fuel_type, ', ', Sum(litres) ) as "fuel type | litres"
From      tmp
Group By fuel_type

     __________________
|fuel type | litres|
|------------------|
|diesel, 20.00     |
|------------------|
|petrol, 30.00     |
 ------------------
    1. If it was 2 rows of data in 2 columns (fuel_type and litres):
    Select    fuel_type, Sum(litres) as litres
From      tmp
Group By  fuel_type

     _________________
|fuel_type| litres|
|---------|-------|
|diesel   |  20.00|
|---------|-------|
|petrol   |  30.00|
 -----------------
    1. If it should be 1 row of data in 1 column named "fuel_type | litres":
    Select    Distinct STRING_AGG(Concat( fuel_type, ', ', litres ), ' 
') Over() as "fuel type | litres"
From      ( Select    fuel_type, Sum(litres) as litres
            From      tmp
            Group By  fuel_type
          )

     __________________
|fuel type | litres|
|------------------|
|diesel, 20.00     |
|petrol, 30.00     |
 ------------------

    See the fiddle here.

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

Back To Top
Search