CODEWITHSUNDEEP
sqlinner-joinaverageaggregate-functions
ERJAN
ERJAN

Reputation: 24500

How to aggregate and count avg value by groups from 2 tables in sql?

I have 2 tables , department and employee. I need to get department name and average age of it.

create table Dep(name_dep char, id_dep int);

insert into Dep values("econ", 1);
insert into Dep values("credit", 2);
insert into Dep values("energy", 3);
insert into Dep values("retail", 4);
insert into Dep values("manufactury", 5);

create table Emp(id_emp int, id_dep int, age int, person_name char, salary int );
insert into Emp values(1, 1, 23, 'john', 200);
insert into Emp values(3, 2, 3, 'dalbai', 100);
insert into Emp values(6, 3, 53, 'borat', 300);
insert into Emp values(7, 1, 63, 'erjan', 1600);
insert into Emp values(9, 2, 73, 'sergey', 1000);
insert into Emp values(8, 5, 83, 'lucy', 20);
insert into Emp values(90, 4, 93, 'mike', 1200);

How to select dept name and avg age of employees in that dept?

SELECT name_dep, average_age
FROM Emp e
INNER JOIN
(
    SELECT name_dep, AVG(age) AS average_age
    FROM Dep d
    GROUP BY id_dep
) d
    ON e.id_dep = d.id_dep

Upvotes: 1

Views: 79

Answers (4)

Apurw
Apurw

Reputation: 7

SELECT d.name_dep,AVG(e.age)
FROM Dep d JOIN Emp e ON d.id_dep = e.id_dep
GROUP BY d.name_dep;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

YOu can try below -

DEMO

select name_dep,avg(age)
from emp a inner join dep b on a.id_dep=b.id_dep
group by a.id_dep,name_dep

Upvotes: 1

axnet
axnet

Reputation: 5790

you can do something like

SELECT 
 d.name_dep, AVG(age) as avg_age 
FROM 
 Emp e, Dep d 
WHERE 
 e.id_dep = d.id_dep 
GROUP BY 
 d.name_dep

Upvotes: 1

forpas
forpas

Reputation: 164089

Join the tables, group by id_dep, name_dep and then get the average age:

select
  d.name_dep, avg(age) average_age
from Dep d inner join Emp e 
on e.id_dep = d.id_dep
group by d.id_dep, d.name_dep

See the demo.
Results:

| name_dep    | average_age |
| ----------- | ----------- |
| econ        | 43          |
| credit      | 38          |
| energy      | 53          |
| retail      | 93          |
| manufactury | 83          |

Upvotes: 1

Related Questions