CODEWITHSUNDEEP
mysqlsqljoingroup-by
Chris Farrugia
Chris Farrugia

Reputation: 1046

Creating a join where I pull a count from another table

I have a table with real estate agent's info and want to pull firstname, fullname, and email from rets_agents.

I want to then get a count of all of their sales from a different table called rets_property_res_mstr.

I created a query that doesn't work yet so I need some help.

SELECT r.firstname, r.fullname, r.email
from rets_agents r
LEFT JOIN rets_property_res_mstr
     ON r.email = rets_property_res_mstr.ListAgentEmail
LIMIT 10;



I'm not sure how to get the count in this.

Upvotes: 0

Views: 31

Answers (2)

Mfer123
Mfer123

Reputation: 40

I would consider using a CTE for this:

WITH sales as (
SELECT ListAgentEmail, count(*) count_of_sales
FROM rets_property_res_mstr
GROUP BY ListAgentEmail
)

SELECT r.firstname, r.fullname, r.email, count_of_sales
from rets_agents r
LEFT JOIN sales
     ON r.email = sales.ListAgentEmail
LIMIT 10;

Upvotes: 0

GMB
GMB

Reputation: 222492

You seem to be looking for aggregation:

SELECT a.firstname, a.fullname, a.email, COUNT(p.ListAgentEmail) cnt
FROM rets_agents a
LEFT JOIN rets_property_res_mstr p ON r.email = p.ListAgentEmail
GROUP BY a.firstname, a.fullname, a.email
ORDER BY ?
LIMIT 10;

Note that, for a LIMIT clause to really make sense, you need a ORDER BY clause so you get a deterministic results (otherwise, it is undefined which records will be shown) - I added that to your query with a question mark that you should replace with the relevant column(s).

Upvotes: 2

Related Questions