SELECT INITCAP(CONCAT(SPLIT_PART(pers.nom_persona, ' ', 1), ' ', pers.ape_pate_pers, ' ', SUBSTRING(pers.ape_mate_pers, 1, 1), '.')) AS nombre_estu,
-- CONCAT(pers.nom_persona,' ', pers.ape_pate_pers, ' ', pers.ape_mate_pers),
aw_estu.*
FROM (SELECT award_x_estu._id_estudiante,
SUM(award_x_estu.award_points) as count_tot,
COUNT(award_x_estu.award_points) FILTER (WHERE award_x_estu.award_points = 1) AS count_pos,
COUNT(award_x_estu.award_points) FILTER (WHERE award_x_estu.award_points = -1) AS count_neg
FROM (SELECT p.*,
na.title_award,
na.award_points
FROM (SELECT t._id_estudiante,
(t.unw->>'idx') AS idx_award,
(t.unw->>'id_award')::integer AS id_award,
(t.unw->>'desc_award') AS desc_award,
(t.unw->>'fecha_award') AS fecha_award
FROM (SELECT _id_estudiante,JSONB_ARRAY_ELEMENTS(json_awards) AS unw
FROM notas.notas_estudiante
WHERE _id_main = 7342) t) p,
notas.award na) award_x_estu
GROUP BY award_x_estu._id_estudiante) aw_estu,
persona pers
WHERE aw_estu._id_estudiante = pers.nid_persona order by count_pos desc limit 5
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.