Resolved: 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:

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

If you have better answer, please add a comment about this, thank you!

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

Resolved: navigate and redirect not redirecting to path in react router

Resolved: Time Complexity: Find the number of people who know a secret leetcode 2327

Resolved: Best way to create 3d matrix of variables in PULP

Leave A Reply