[Resolvido] count + group by (exercicio)

[dyggos 0]

boa noite amigos, estou estudando sql e utilizo o schema HR. tenho esse exercicio para fazer mas não consigo rodar, se tirar o GROUP by e o count ele roda o que tem de errado???

 

EXERCICIO:

Fazer uma pesquisa e retornar os 3 primeiros departamentos ordenados pela

quantidade de funcionários; (ORDENADOS POR QUANTIDADE DE FUNCIONÁRIOS. “LIMITE”)

 

select  count(employees.employee_id)"FUNCIONÁRIOS",
       departments.department_name as DEPARTAMENTO

 from employees
     join departments on employees.department_id = departments.department_id
     join locations on departments.location_id = locations.location_id
     join countries on locations.country_id = countries.country_id
     join regions on countries.region_id = regions.region_id
     join jobs on employees.job_id = jobs.job_id
       WHERE ROWNUM < 4

order by employees.employee_id
group by departments.department_name

[Motta 645]

--troque

order by employees.employee_id
group by departments.department_name

--por

group by departments.department_name
order by employees.employee_id

[dyggos 0]

  Em 27/11/2011 at 10:07, Motta disse:

--troque

order by employees.employee_id
group by departments.department_name

--por

group by departments.department_name
order by employees.employee_id

 

 

Bom dia Motta,

 

tentei inverter conforme sugeriu mas não funcionou, se eu colocar essa consulta:

select  employees.employee_id,
       departments.department_name as DEPARTAMENTO

 from employees
     join departments on employees.department_id = departments.department_id
     join locations on departments.location_id = locations.location_id
     join countries on locations.country_id = countries.country_id
     join regions on countries.region_id = regions.region_id
     join jobs on employees.job_id = jobs.job_id
       WHERE ROWNUM < 4

funciona, mas se colocar group by ou order by já não funciona, mesmo eu colocando count(select employees.employee_id),

[Motta 645]

SELECT  FUNCIONARIOS,DEPARTAMENTO
FROM
(
select  count(employees.employee_id) FUNCIONARIOS,
       departments.department_name as DEPARTAMENTO       
 from employees
     join departments on employees.department_id = departments.department_id
     join locations on departments.location_id = locations.location_id
     join countries on locations.country_id = countries.country_id
     join regions on countries.region_id = regions.region_id
     join jobs on employees.job_id = jobs.job_id
group by departments.department_name
order by 1 desc
) where rownum <= 4

[dyggos 0]

  Em 27/11/2011 at 15:24, Motta disse:

SELECT  FUNCIONARIOS,DEPARTAMENTO
FROM
(
select  count(employees.employee_id) FUNCIONARIOS,
       departments.department_name as DEPARTAMENTO       
 from employees
     join departments on employees.department_id = departments.department_id
     join locations on departments.location_id = locations.location_id
     join countries on locations.country_id = countries.country_id
     join regions on countries.region_id = regions.region_id
     join jobs on employees.job_id = jobs.job_id
group by departments.department_name
order by 1 desc
) where rownum <= 4

 

Motta muito obrigado amigão, tenho a impressão de que o fórum funciona por tua causa.

Vlw mesmo.

 

Abraço

[Motta 645]

:) (só rindo), mas valeu.