postgres sql query using group by clause and sub query

Question

Hello I have a table w/the following information.

agent (agent_id, salary, city, country)

I'm trying to make a query that fullfils this request...

'List the number of agents and total salary for agents in each country with at least 6 cities.'

My issue is on how to group the unquie countries and count the number of cities related to the country. I'm not too sure on how to perform the sub query needed, or the group by clause. I'm trying stuff like the following...

select COUNT(agent_id) as numOfAgents, SUM(salary) as Salary, DISTINCT country
from agent
where city = (select COUNT(city) from agent where city > '5')

I know this query doesn't work, and I need to introduce the group by clause somehow.

Giorgos Betsos · Accepted Answer · 2016-01-31 20:41:11Z

2

This is a predicate for the HAVING clause of a GROUP BY statement:

SELECT COUNT(agent_id) AS numOfAgents, SUM(salary) as Salary, country 
FROM agent 
GROUP BY country
HAVING COUNT(DISTINCT city) > 5

The COUNT aggregate function that appears in the HAVING clause, counts the number of distinct cities per country.

answered Jan 31, 2016 at 20:41

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3622460 Over a year ago

Thank you for the explanation.

Collectives™ on Stack Overflow

postgres sql query using group by clause and sub query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related