Sum of groups in an SQL

Asked

Viewed 99 times

1

I need to do the TOTAL sum per office of a field of hours that is in decimal format, in addition, perform the conversion of it.

I can do the sum of the employees, but I can’t add up to "groups" of the secretariat. Ex:

inserir a descrição da imagem aqui

With my SQL it brings that employee 1233 has 20 hours and employee 4123 has 8 hours. But that’s not what I need. I need the sum to be on top of the secretariats. Office 10, 30 hours. Office 7, 8 hours.

I’m a little lost in the assembly of this query.

I got to that point and I’m stuck. Below is my query:

SELECT
    S1.id,S1.cd_secretaria,
    to_char(to_timestamp(sum((S2.nr_horas) * 60)), 'MI:SS')
FROM
    sch_sismapa.tb_servidor S1
JOIN
    sch_sismapa.tb_he_norm_diurno S2 on (S2.id_servidor = S1.id)
WHERE
    S1.id_referencia = '5'
GROUP BY
    S1.id,
    S1.cd_secretaria
ORDER BY
    S1.cd_secretaria,
    S1.id;

I hope my doubt is clear.

postgresql query

  • What kind of data field hours, interval? In the desired result you want only the secretariats and the corresponding total hours of each of them?

  • The Field Type hours is real. Yes I want the total hours per office.

3 answers

0

You can use an aggregation function as SUM():

SELECT S1.id,S1.cd_secretaria, SUM(S1.id,S1.cd_secretaria), to_char(to_timestamp(sum((S2.nr_horas) * 60)), 'MI:SS') FROM sch_sismapa.tb_servidor S1 JOIN sch_sismapa.tb_he_norm_diurno S2 on (S2.id_servidor = S1.id) WHERE S1.id_referencia = '5' GROUP BY S1.id,S1.cd_secretaria ORDER BY S1.cd_secretaria,S1.id

0


Try:

SELECT S1.cd_secretaria, to_char(SUM(S2.nr_horas)*60*'1 min'::interval, 'DD HH24:MI')
FROM sch_sismapa.tb_servidor S1 JOIN sch_sismapa.tb_he_norm_diurno S2 ON (S2.id_servidor = S1.id)  
WHERE S1.id_referencia = '5'  
GROUP BY S1.cd_secretaria  
ORDER BY S1.cd_secretaria;

Evaluate whether or not to include S1.id case cd_secretaria can be repeated for different id.

If the sum never exceeds 24 hours then you can remove the DD of function to_char.

  • Fantastic. Thank you so much for your help. This way you made completely meets my question.

0

I don’t know if I understand your question correctly, but if you wish to recover the total amount of hours per secretary, the thing may be simpler than it seems.

Assuming you have a data structure like this:

CREATE TABLE tb_horas
(
  id_funcionario INTEGER,
  id_secretaria INTEGER,
  nu_horas INTEGER
);

INSERT INTO tb_horas ( id_funcionario, id_secretaria, nu_horas ) VALUES
( 1233, 10, 10 ),
( 4123,  7,  5 ),
( 5545, 10, 10 ),
( 1233, 10, 10 ),
( 4123,  7,  3 );

The consultation would be something like:

SELECT
  id_secretaria,
  to_char((sum(nu_horas)) * '1 hour'::interval,'HH24:MI') AS total
FROM
  tb_horas
GROUP BY 
  id_secretaria;

Exit:

| id_secretaria | total |
|---------------|-------|
|            10 | 30:00 |
|             7 | 08:00 |

See working on Sqlfiddle

Browser other questions tagged postgresql query

You are not signed in. Login or sign up in order to post.