Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

91
Vistas
Month and year from date Postgresql

I need to extract from a table that has the following columns "responsable", "fecha_contratado" the following data:

ORIGIN:
fecha_contratado || responsable

"2016-08-04";"sonia"

"2016-05-09";"mercedes"

"2016-03-01";"rebeca"

"2017-02-20";"rebeca"

"2017-01-02";"julia"

"2016-01-11";"anamgarcia"

"2016-06-20";"rebeca"

"2017-01-16";"julia"

"2016-09-26";"sonia"

"2017-03-06";"victoria"

"2016-09-28";"daniel"

"2016-01-07";"emilio"

"2016-02-08";"valle"

"2016-01-14";"mercedes"

"2016-11-14";"mercedes"

"2017-03-09";"alba"

For each "responsable"(responsible person) and year a row with the following data:

Anno | Responsible | January | February | .... | total

"anno": year

"responsable" : responsible

"Enero"(January): rows that are for that year, January and responsible in question (count)

February: same as January but with February month.

March --- December .: equal

Total: total of the year, for that year and responsible (count)

anno | responsable | Enero --- Diciebre | Total

2017;"alba";0;1;0;0;0;0;0;0;0;0;0;0;1

2017;"mercedes";0;0;1;0;0;0;0;0;0;0;0;0;1

2016;"alba";0;0;2;0;0;0;0;0;0;0;0;0;2

Now I get that, but I for a year and responsible I get more than a row, and I want only a row for a year and responsible, concrete

select  
anno,
tecnico_rrhh,
sum(case when mes = 1 then total else 0 end) as enero,
sum(case when mes = 2 then total else 0 end) as febrero,
sum(case when mes = 3 then total else 0 end) as marzo,
sum(case when mes = 4 then total else 0 end) as abril,
sum(case when mes = 5 then total else 0 end) as mayo,
sum(case when mes = 6 then total else 0 end) as junio,
sum(case when mes = 7 then total else 0 end) as julio,
sum(case when mes = 8 then total else 0 end) as agosto,
sum(case when mes = 9 then total else 0 end) as septiembre,
sum(case when mes = 10 then total else 0 end) as octubre,
sum(case when mes = 11 then total else 0 end) as noviembre,
sum(case when mes = 12 then total else 0 end) as diciembre,
sum(coalesce(total,0)) as total
from (
select  
  empleado.fecha_contratado as alta,
  extract(month from empleado.fecha_contratado) as mes,
  extract(year from empleado.fecha_contratado) as anno,
  count(1) as total,
  usuario_responsable.username  as tecnico_rrhh 
from rrhh.empleado as empleado
LEFT JOIN commons.usuario as usuario_responsable 
  on empleado.responsable = usuario_responsable.id
where usuario_responsable.username is not null 
group by mes,empleado.fecha_contratado, tecnico_rrhh) altas
group by alta,anno, tecnico_rrhh 
order by tecnico_rrhh;

The closest thing I've got to get is the following query, but it repeats rows for the same "responsible" and "year", when I need to pull out a single row for each "responsible year".

Can somebody help me. Thank you very much.

over 4 years ago · Santiago Trujillo
1 Respuestas
Responde la pregunta

0

try aggregating your set:

with p as (
select  
anno,
tecnico_rrhh,
sum(case when mes = 1 then total else 0 end) as enero,
sum(case when mes = 2 then total else 0 end) as febrero,
sum(case when mes = 3 then total else 0 end) as marzo,
sum(case when mes = 4 then total else 0 end) as abril,
sum(case when mes = 5 then total else 0 end) as mayo,
sum(case when mes = 6 then total else 0 end) as junio,
sum(case when mes = 7 then total else 0 end) as julio,
sum(case when mes = 8 then total else 0 end) as agosto,
sum(case when mes = 9 then total else 0 end) as septiembre,
sum(case when mes = 10 then total else 0 end) as octubre,
sum(case when mes = 11 then total else 0 end) as noviembre,
sum(case when mes = 12 then total else 0 end) as diciembre,
sum(coalesce(total,0)) as total
from (
select  
  empleado.fecha_contratado as alta,
  extract(month from empleado.fecha_contratado) as mes,
  extract(year from empleado.fecha_contratado) as anno,
  count(1) as total,
  usuario_responsable.username  as tecnico_rrhh 
from rrhh.empleado as empleado
LEFT JOIN commons.usuario as usuario_responsable 
  on empleado.responsable = usuario_responsable.id
where usuario_responsable.username is not null 
group by mes,empleado.fecha_contratado, tecnico_rrhh) altas
group by alta,anno, tecnico_rrhh 
order by tecnico_rrhh
)
select anno, tecnico_rrhh, sum(enero), sum(febrero), sum(marzo), sum(abril), sum(mayo), sum(junio), sum(julio), sum(agosto), sum(septiembre), sum(octubre), sum(noviembre), sum(diciembre)
from p
group by anno, tecnico_rrhh;
over 4 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda