sqlheidisql

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


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!


Solution

  • 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