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