--------------------------------- WITH tabla_nueva AS ( WITH tabla_estudents AS ( WITH aw AS (SELECT (t.unw->>'id_award')::INTEGER AS id_award, (t.unw->>'fecha_award')::DATE::TEXT AS fecha_award, (t._id_estudiante)::INTEGER AS _id_estudiante FROM (SELECT _id_estudiante,JSONB_ARRAY_ELEMENTS(json_awards) AS unw FROM notas.notas_estudiante WHERE _id_main=2652) AS t) SELECT _id_estudiante, count(na.award_points) filter (where na.award_points = 1) as count_pos, count(na.award_points) filter (where na.award_points = -1) as count_neg, count(na.award_points) as count_tot, ARRAY_TO_JSON(ARRAY_AGG(JSONB_BUILD_OBJECT('id_award',aw.id_award, 'fecha_award',aw.fecha_award, 'award_points',na.award_points ) ) ) AS awards FROM aw, notas.award na WHERE aw.id_award = na.id_award GROUP BY _id_estudiante) select * from tabla_estudents SELECT nc.*, tn.awards FROM notas.__calificaciones_get_estudiante_main__11(7365) nc LEFT JOIN tabla_estudents tn ON (nc._id_estudiante = tn._id_estudiante)

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.