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

Error code subquery uses ungrouped column from outer query – Postgresql

Can anyone help me whats wrong with my code

SELECT agent.agent_name AS agent_name,
       (SELECT COUNT(assigned.id_agent))AS itung,
       (SELECT COUNT(ticket.id_agent) WHERE ticket.id_status = 4) AS itung2
FROM agent
  LEFT JOIN assigned ON agent.id = assigned.id_agent
  LEFT JOIN ticket ON agent.id = ticket.id_agent
GROUP BY agent.id

I got error code subquery uses ungrouped column "ticket.id_status" from outer query

LINE 3: (SELECT COUNT(ticket.id_agent)WHERE ticket.id_status = 4)AS …

Tags:

2

Answers


  1. 0

    It’s a bit unclear to me what you are trying to achieve, but I think you are looking for the FILTER.

    But as agent.agent_name is your only un-grouped column, you have to group by that:

    SELECT agent.agent_name AS agent_name,
       COUNT(assigned.id_agent) AS itung,
       COUNT(ticket.id_agent) FILTER (WHERE ticket.id_status = 4) AS itung2
FROM agent
  LEFT JOIN assigned ON agent.id = assigned.id_agent
  LEFT JOIN ticket ON agent.id = ticket.id_agent
GROUP BY agent.agent_name;
    Login or Signup to reply.
  2. 0

    Looking at your query, you are clearly missing ticket.id_status in group by clause.
    You can either include it in group by clause;

    OR

    The query can be further simplified by aggregate function to calculate the value of itung2 as below :

    SELECT agent.agent_name AS agent_name,
    COUNT(assigned.id_agent) AS itung,
    COUNT(ticket.id_agent) AS itung2
FROM agent
LEFT JOIN assigned ON agent.id = assigned.id_agent
LEFT JOIN ticket ON agent.id = ticket.id_agent AND ticket.id_status = 4
GROUP BY agent.id, agent.agent_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search