Group by

Permite calcular totales de grupo, se usa solo cuando se necesite una función , si se necesita mostar por  nombre o por algo solo se ordena

select
from
where
group by Exp-grupo

----------------------------------------------------------------------------------------------


--No se puede poner asterisco
select * from employees e join departments d  on d.department_id=e.department_id  order by d.department_name;

select e.department_id from employees e group by e.department_id;

select d.department_name from employees e join departments d  on d.department_id=e.department_id group by  d.department_name;

select d.department_name from employees e join departments d  on d.department_id=e.department_id group by  d.department_id, d.department_name;

-----------------------Total de salarios de Accouting 20300
--------Suma
select d.department_name , sum(e.salary)from employees e join departments d  on d.department_id=e.department_id group by  d.department_id, d.department_name;

-------Promedio 
select d.department_name , sum(e.salary), avg(e.salary) from employees e join departments d  on d.department_id=e.department_id group by  d.department_id, d.department_name;


Finance 51600 8600
Shipping 156400 3475,555555555555555555555555555555555556
Public Relations 10000 10000
Executive 58000 19333,3333333333333333333333333333333333
Purchasing 24900 4150
Administration 4400 4400
Accounting 20300 10150
Human Resources 6500 6500
IT 28800 5760
Sales 304500 8955,882352941176470588235294117647058824
Marketing 19000 9500


---Ordenar por el  d.department_id, d.department_name,  e.employee_id 
select d.department_name, sum(e.salary), avg(e.salary) from employees e join departments d  on d.department_id=e.department_id group by  d.department_id, d.department_name,  e.employee_id ;


Otras funciones


------Como todas las funciones son  en una agrupacion ´por departamento entonces todas las  funciones se ponen en una consulta

select d.department_name , sum(e.salary) , max(e.salary), avg(e.salary), min(e.salary) , variance (e.salary) from employees e join departments d  on d.department_id=e.department_id group by  d.department_id, d.department_name;


Mostrar departamentos  con menos de 3 empleados

select d.department_name, sum(e.salary), avg(e.salary), count(e.employee_id) from employees e right join departments d  on d.department_id=e.department_id group by
d.department_id, d.department_name having count (e.employee_id ) <3;


select count (*) from employees;

---- cuenta id aunque hayan repetidas
select count (department_id) from employees;

----Cuenta id sin repetir , sumar  salarios distintos , promediar salarios distintos
select  count (distinct department_id) from employees;