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.
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;