CODEWITHSUNDEEP
postgresqlsubquery
theDrifter
theDrifter

Reputation: 1706

Postgresql 9.4 Subquery missing from-clause

I have the following subquery. In the end i want to count for every agent for all this users the controllers. So in the substring i count the controllers for all the agent his users and then i want to sum them up.

The substring itself is running, however i get this error when i run the complete query

ERROR:  missing FROM-clause entry for table "sub"
LINE 2: a.id as a_id, SUM(sub.c_count )  as c_count,



SELECT 
a.id as a_id, SUM(sub.c_count )  AS c_count,
(
SELECT u.id,  COUNT(c.user_id) AS c_amount
FROM users u
JOIN controllers c  ON (c.user_id = u.id)
GROUP BY
u.id
)sub
from  agents a;

Upvotes: 2

Views: 799

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

You are getting this error because the subquery which you have aliased as sub returns not only multiple records, but also multiple values per record, but it appears in the SELECT list. Generally, only scalars (single values) or subqueries which return a single value can appear in the SELECT list. You have two options, you can either modify sub to return a single value, or you can try to join this subauery to the outer query. Given that you mentioned the subquery is supposed to do some aggregation for each user or agent, my guess is that joining is the answer. In the query below I join sub to the outer query on the agent ID matching the user ID from sub.

SELECT a.id AS a_id,
       COALESCE(sub.c_amount, 0) AS c_count
FROM agents a
LEFT JOIN
(
    SELECT u.id, COUNT(c.user_id) AS c_amount
    FROM users u
    INNER JOIN controllers c
        ON c.user_id = u.id
    GROUP BY u.id
) sub
    ON a.id = sub.id

Upvotes: 2

Related Questions