CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-function
Ascerd
Ascerd

Reputation: 1

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

Upvotes: -4

Views: 100

Answers (2)

d r
d r

Reputation: 7846

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.

Upvotes: 0

Esanju Babatunde
Esanju Babatunde

Reputation: 107

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;

Upvotes: 0

Related Questions