SQL SUM up specific rows without subquery returning more than one row

Question

There are a few posts that seem similar to this problem, but I cannot find the solution to this problem through those posts.

I have the following two tables that I am working with (I'm not posting the full table, just as much is needed to understand the problem):

Table 1: employee

emp_id	first_name	last_name
102	Michael	Scott
108	Jim	Halpert

Table 2: works_with

emp_id	client_id	total_sales
102	401	267,000
102	406	15,000
108	402	22,500
108	403	12,000

If this table data is not sufficient, I can go back and add more.

The issue I am having is with this code:

SELECT e.emp_id AS ID, 
e.first_name AS 'First Name', 
e.last_name AS 'Last Name', 
ww.total_sales = (SELECT SUM(ww.total_sales) 
                 FROM works_with
                 WHERE e.emp_id = ww.emp_id
                 ) AS Sales
FROM (employee e, works_with ww)
INNER JOIN works_with ON ww.emp_id = e.emp_id;

When I run the code like this, I get the error: "SQL Error (1242): Subquery returns more than 1 row."

I saw a solution online that said to add "Any" before the subquery, but this is the resulting table:

ID	First Name	Last Name	Sales
102	Michael	Scott	0

This is what I want the resulting table to look like:

ID	First Name	Last Name	Sales
102	Michael	Scott	282,000
108	Jim	Halper	34,500

I am confused as to how I would fix this. Any help is greatly appreciated!

Answer 1

This should give you the results you need where table 1 is the first table you mentioned and table 2 is the second

SELECT
    T1.*
    SUM(T2.TOTAL_SALES)
FROM <TABLE1> T1
    JOIN <TABLE2> T2 ON T1.EMP_ID = T2.EMP_ID
GROUP BY
    T1.EMP_ID, T1.FIRST_NAME, T1.LAST_NAME