CODEWITHSUNDEEP
sqlselecthana
Anis Fendi
Anis Fendi

Reputation: 1

SQL SELECT query with two tables containing sum function

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

Upvotes: 0

Views: 672

Answers (2)

SeanBartram
SeanBartram

Reputation: 45

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

Upvotes: 0

jahanzeb naqvi
jahanzeb naqvi

Reputation: 48

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;

Upvotes: 1

Related Questions