CODEWITHSUNDEEP
sqlpostgresql
Muhammad Rafi Bahrur Rizki
Muhammad Rafi Bahrur Rizki

Reputation: 451

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

I have a problem with a data query where I query a single column like this:

SELECT a.ad_morg_key, count(a.sid_mpenduduk_key) AS total_population 
FROM sid_mpenduduk a 
GROUP BY a.ad_morg_key;

1

and it really works. But when I query with multiple columns with a query like this:

SELECT a.ad_morg_key, b."name", 
count(b.sid_magama_key) AS total, 
count(b.sid_magama_key)::float / (SELECT count(a.sid_mpenduduk_key) 
FROM sid_mpenduduk a 
GROUP BY a.ad_morg_key)::float * 100::float AS percentage,
(SELECT count(a.sid_mpenduduk_key) FROM sid_mpenduduk a GROUP BY a.ad_morg_key) AS total_population
FROM sid_mpenduduk a
INNER JOIN sid_magama b ON a.sid_magama_key = b.sid_magama_key
GROUP BY a.ad_morg_key, b."name";

But it fails with:

ERROR:  more than one row returned by a subquery used as an expression

I want the final result like this :

2

Upvotes: 0

Views: 7726

Answers (3)

James K. Lowden
James K. Lowden

Reputation: 7837

You're getting burned by

GROUP BY ...
(SELECT count(a.sid_mpenduduk_key) 
 FROM sid_mpenduduk a 
 GROUP BY a.ad_morg_key) AS total_population

because the outer GROUP BY wants a scalar, but the subquery is producing a count for each a.ad_morg_key.

I don't write my queries that way. Instead, produce a virtual table,

SELECT a.ad_morg_key, b."name", 
...
JOIN 
(SELECT ad_morg_key, 
        count(sid_mpenduduk_key) as N
 FROM sid_mpenduduk
 GROUP BY ad_morg_key) AS morgs
on ad_morg_key = morgs.ad_morg_key

That way, you have the count for each row as N, and you can divide at will,

count(b.sid_magama_key)::float / morgs.N

and, if you get tripped up, you'll have many more rows than you expected instead of an error message.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a correlation clause instead:

(SELECT count(a.sid_mpenduduk_key) 
 FROM sid_mpenduduk a2 
 WHERE a2.ad_morg_key = a.ad_morg_key
) AS total_population

I'm not sure if the subquery is really necessary. So, you might consider asking a new question with sample data, desired results, and a clear explanation of what you are trying to do.

Upvotes: 1

Horacio_Colbert
Horacio_Colbert

Reputation: 176

Your subquery is grouped by a.ad_morg_key so it will get you a row for each different value of a.ad_morg_key.

In general terms each subquery in a SELECT statement should return a single value. Suppose you have the following table called A.

A_key A_value
A1 200
A2 200

If you execute

SELECT (SELECT A_KEY FROM A) as keys
FROM A

the subquery (SELECT A_KEY FROM A) returns

A_key
A1
A2

so what should be the value for keys? SQL cannot handle this decision so you should pick one of the values or aggregate them into a single value.

Upvotes: 1

Related Questions