Getting all rows where one column is in a set within the same table (MySQL)

So I've got a MySQL开发者_高级运维 table with the following columns:

PROPERTY_ID
AGENT_ID
STATUS
SALE_DATE
SALE_PRICE
ZIP

I want to get the total sales for all agents who have sold at least one property in a given ZIP code. Meaning, if Bob Smith's AGENT_ID is linked to at least one property in ZIP 90210, then I want all of Bob Smith's sales included in the total.

I know I can do this in multiple steps with something like:

CREATE TEMPORARY TABLE `AGENTS` (`AGENT_ID` INT);
INSERT INTO `AGENTS` SELECT DISTINCT `AGENT_ID` FROM `PROPERTIES` WHERE ZIP = 90210;
SELECT SUM(p.`SALE_PRICE`) FROM `PROPERTIES` p, `AGENTS` a WHERE p.`AGENT_ID` = a.`AGENT_ID`;

What I'm wondering is, can I accomplish this without dumping the agent IDs into a temporary table to join on? It seems like there would be a way to join the PROPERTIES table on itself for this purpose but I'm not sure how. Any ideas would be much appreciated...

SELECT
    AGENT_ID,
    SUM(SALE_PRICE)
FROM PROPERTIES
WHERE AGENT_ID IN (SELECT AGENT_ID FROM PROPERTIES WHERE ZIP = 90210)
GROUP BY AGENT_ID;

This will give a separate total for each agent that sold something in 90210.
Although you question doesn't say this, it suggests this is what you want.

If I understand, you don't actually need a JOIN and can accomplish this with a select in the where clause. The IN clause gives you all agents who've sold anything in 90210, but doesn't limit the results of the SUM() to those property records in 90210.

SELECT SUM(SALE_PRICE) 
FROM properties
WHERE AGENT_ID IN (SELECT AGENT_ID FROM properties WHERE ZIP = 90210);

If you want sums of each individual agent, add a GROUP BY:

SELECT AGENT_ID, SUM(SALE_PRICE) 
FROM properties
WHERE AGENT_ID IN (SELECT AGENT_ID FROM properties WHERE ZIP = 90210)
GROUP BY AGENT_ID;