CODEWITHSUNDEEP
sqlpostgresql
Sahbaz
Sahbaz

Reputation: 1272

Count occurrences in many to many

In my database i have following tables:

Person (
id,
name,
agentId
)

Agent (
id,
title
)

Agency (
id,
name
)

AgentAgency (
id,
agentId,
agencyId
)

I need query that will get all info about Person -> Agents with extra attribute numberOfAgencies that will show number of agencies of each agent, AND i need to show one more attribute agencyName that will show me name of first or only agency that user have (i need it in case agent have only 1 agency).

I tried something like this but without any success. 

 SELECT *, COUNT (aa.agentId) as numberOfAgencies
 FROM agentAgencies as aa
 LEFT JOIN agent as a ON a.id = aa.agentId
 LEFT JOIN agency as ag ON aa.agencyId= ag.id
 LEFT JOIN person as p ON p.id = ag.personId
 GROUP BY ag.id, aa.id, p.id, a.id

For example i expect response like this: PersonName John, AgencyName Cool Agency, numberOfAgencies 4

Upvotes: 0

Views: 92

Answers (1)

Andy
Andy

Reputation: 3743

SELECT
MAX(p.Name) PersonName,
count(a.id) NoOfAgencies,
MAX(a.name) AgencyName
FROM persons p
LEFT OUTER JOIN agent g ON g.Id=p.agentId
LEFT OUTER JOIN AgentAgency aa ON aa.agentId = g.Id
LEFT OUTER JOIN Agency a on a.id = aa.agencyId
GROUP BY a.Id

Upvotes: 2

Related Questions