Joesph Joesph - 1 year ago 72
SQL Question

Oracle sql query - getting SUM() from another table using GROUP BY

There are two tables:

Employee table:

employee_id, lname
E01 | Smith
E02 | Johnson
E03 | Williams
E04 | Jones

Salary table:

employee_id, paid
E01 | 199
E04 | 751
E01 | 599
E02 | 299
E03 | 259
E03 | 357
E02 | 671
E04 | 130

How do I query only the last names of the employee, and their total salary earned? My attempt drew too many rows:

SELECT lname, total_paid
FROM Employee
CROSS JOIN (SELECT SUM(paid) AS total_paid FROM Salary GROUP BY employee_id);

My problem is, I need to
the total earned from the table paid, meaning I need to use
if I'm not mistaken. However, I only want to select lname and the total...

Answer Source

You need to join the tables and then aggregate:

SELECT e.lname, SUM(s.paid) as total_paid
FROM Employee e JOIN
     Salary s
     ON e.employee_id = s.employee_id
GROUP BY e.lname;


  • Use a LEFT JOIN if some employees have no salaries.
  • This is the standard way to do what you want. You could use a correlated subquery as well.
  • The unaggregated column in the SELECT (lname) should be the one in the GROUP BY.
  • All the columns are qualified, meaning they have table aliases.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download