How can I get, in a single SELECT query, a result from two tables, one of which contains SUM function ?
Table SALARY
| EMPLOYEE | AMOUNT |
|---|---|
| John | 1000 |
| Bob | 2000 |
| Phill | 4000 |
| John | 500 |
| Bob | 1000 |
| Bob | 100 |
Table ADRESS
| EMPLOYEE | CITY |
|---|---|
| John | LA |
| Bob | NYC |
| Phill | LA |
Expected result:
| EMPLOYEE | CITY | AMOUNT |
|---|---|---|
| John | LA | 1500 |
| Bob | NYC | 3100 |
| Phill | LA | 4000 |
I know how to get the sum of salaries:
select EMPLOYEE, SUM(AMOUNT) from SALARY group by EMPLOYEE
But I don't know how to combine the result with the ADDRESS table.
I tried this :
select S.EMPLOYEE, SUM(S.AMOUNT), A.city from SALARY S, ADDRESS A
group by S.EMPLOYEE
=> "not a GROUP BY expression: 'A.CITY' must be in group by clause"
So I added A.CITY in the group by clause :
select S.EMPLOYEE, SUM(S.AMOUNT), A.city from SALARY S, ADDRESS A group by S.EMPLOYEE, A.city
Which gave me this result :
| EMPLOYEE | SUM(AMOUNT) | CITY |
|---|---|---|
| John | 3000 | LA |
| John | 1500 | NYC |
| Bob | 3100 | NYC |
| Phill | 8000 | LA |
| Bob | 6200 | LA |
| Phill | 4000 | NYC |
CodePudding user response：
select S.EMPLOYEE,A.City, SUM(S.AMOUNT) AS Amount from SALARY S JOIN Address A on S.EMPLOYEE = A.EMPLOYEE group by S.EMPLOYEE,A.City;
CodePudding user response：
try this?
select S.EMPLOYEE, SUM(S.AMOUNT), A.city from SALARY S join ADDRESS A on A.EMPLOYEE=S.EMPLOYEE group by S.EMPLOYEE, A.city