Find greater sum value and show id

Navigation
1

Talk about it, all right?

I have this table

EMPLOYEE_PROJECT Table 

+--------------+-------------+-------+
| rg_empregado | num_projeto | horas |  
+--------------+-------------+-------+
| 20202020     | 5           | 10    |  
+--------------+-------------+-------+
| 20202020     | 10          | 25    |  
+--------------+-------------+-------+
| 30303030     | 5           | 35    | 
+--------------+-------------+-------+
| 40404040     | 20          | 50    | 
+--------------+-------------+-------+
| 50505050     | 20          | 35    | 
+--------------+-------------+-------+

I need to show which project has the most hours. For this I will need to join the projects with the same number, add their hours and show which is the largest sum. The sum I am able to show, it is only necessary to identify which is the number of the project that this sum belongs.

Here's what I've been able to do so far. 

SELECT MAX(HORAS_TOTAIS) AS MAIOR_CARGA_HORARIA FROM
(SELECT DISTINCT NUM_PROJETO, SUM(HORAS) AS HORAS_TOTAIS FROM EMPREGADO_PROJETO
GROUP BY NUM_PROJETO) EMPREGADO_PROJETO

Raphael's answer worked out, I just needed to add the top 1 on the first select and it worked exactly as I wanted it to.

    
asked by anonymous 13.04.2016 / 16:22

3 answers

-1

SELECT num_projeto , (SELECT DISTINCT SUM (HOURS)) AS_TOPS FROM JOB_PRODUCT GROUP BY num_project ORDER BY HOURS_TOTAL DESC

    
13.04.2016 / 16:34
0

I believe this query solves your problem: 

SELECT SUM(HORAS) AS HORAS, NUM_PROJETO FROM EMPREGADO_PROJETO
GROUP BY NUM_PROJETO
ORDER BY HORAS DESC

Result:

    
13.04.2016 / 16:47
0

I think that's how it works, since it's to catch the biggest: 

 SELECT TOP 1 total.horas_totais, total.rg, total.num_projeto
    FROM ( SELECT DISTINCT NUM_PROJETO as num_projeto,
           SUM(HORAS) AS horas_totais,
           RG_EMPREGADO as rg
           FROM EMPREGADO_PROJETO
           GROUP BY NUM_PROJETO
         ) total
    ORDER total.horas_totais DESC;

Or 

SELECT TOP 1 sum(horas) AS TOTAL_HORAS,
       NUM_PROJETO,
       RG_EMPREGADO
FROM  EMPREGADO_PROJETO
GROUP BY NUM_PROJETO ORDER BY TOTAL_HORAS desc;
    
13.04.2016 / 16:45
Request problem - Phonegap + Ajax [duplicate] omnifaces does not recognize CDI configured in tomcat