Find higher sum value and show id

Question

Talk guys, all right?

I have this table

Table EMPREGADO_PROJETO

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

I need to show you which project has the most hours. For this I will need to join the projects with the same number, add up their hours and show what is the largest sum. The sum I’m managing to show, all that remains is to identify which is the project number that this sum belongs to.

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 response worked, I only had to add the top 1 in the first select and it worked exactly as I wanted it.

Answer 1

I think it works that way, 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;

Answer 2

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

Upshot:

Answer 3

SELECT num_projeto, (SELECT DISTINCT SUM(HOURS)) AS HORAS_TOTAIS FROM EMPREGADO_PROJETO GROUP BY num_project ORDER BY HORAS_TOTAIS DESC