Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

89
Views
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 answers
Answer question

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!