CODEWITHSUNDEEP
mysqldatabase
r93
r93

Reputation: 7

Using MySQL to find out an average value

I have 2 tables

One table called employee which has the following fields: Id, fname,lname Another table called Salary which has the follwing fields: Id, salary. The tables have values inside

What i am trying to do is to display the average salary of employees with surname of 'smith'.

I have this query: 

SELECT AVG(Salary) FROM Salary 
UNION 
SELECT * FROM Employee WHERE lname = 'smith'

But i dont think it will work.

Upvotes: 0

Views: 51

Answers (2)

Bellatechie30
Bellatechie30

Reputation: 34

Another alternative to the answer could be written by omitting both the UNION and JOIN operation. TABLE ALIASES have been used to increase readability and to shorten the SQL statement.

SELECT e.Lname,avg(s.Salary)
FROM Employee e,Salary s
WHERE e.Id=s.Id
AND e.lname='Smith'
GROUP BY e.Lname;

You can read more about aliases in the following links: http://www.techonthenet.com/mysql/alias.php http://www.w3schools.com/sql/sql_alias.asp

Upvotes: 0

Chris Lear
Chris Lear

Reputation: 6752

Maybe this:

SELECT lname, AVG(Salary) 
FROM Salary 
JOIN employee ON Salary.Id = employee.Id 
WHERE lname= 'smith' 
GROUP BY employee.lname;

I've added the group by in case you want to remove the where clause.

Upvotes: 1

Related Questions