SQL SELECT query with two tables containing sum function

Question

How can I get, in a single SELECT query, a result from two tables, one of which contains SUM function ?

Table SALARY

Table ADRESS

Expected result:

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 :

Regarding editting the question, for some reason the tables need a blank line above them to render correctly in the question, but the "preview" shows the tables correctly without the additional blank line. I've edited the question and added the blank lines so they show up correctly. — JNevill
– JNevill, Commented Sep 29, 2022 at 15:12
You failed to supply a join criteria in your FROM clause. A good rule to follow is to never use a comma to separate tables in your FROM. Join then correctly: from SALARY S INNER JOIN ADDRESS A on S.Employee = A.Employee. Replace your FROM clause in your last SQL attempt and rerun. By using a comma and not supplying join conditions, you were having your database join every row in SALARY with every row in ADDRESS. — JNevill
– JNevill, Commented Sep 29, 2022 at 15:15
@JNevill And I get your point about never use separate tables in the FROM, but use a JOIN instead. Thanks again — Anis Fendi
– Anis Fendi, Commented Sep 29, 2022 at 15:28

jahanzeb naqvi · Accepted Answer · 2022-09-29 15:18:16Z

1

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;

answered Sep 29, 2022 at 15:18

jahanzeb naqvi

487 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SeanBartram · Accepted Answer · 2022-09-29 15:18:31Z

0

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

answered Sep 29, 2022 at 15:18

SeanBartram

455 bronze badges