WITH award_mes AS (SELECT n_aw.id_award,
n_aw.title_award,
CASE
WHEN res_aw.count_aw is not null then res_aw.count_aw
ELSE 0
END AS count_awards,
CASE
WHEN res_aw.category_award is not null THEN res_aw.category_award
ELSE 'EMPTY'
END AS category_award
FROM notas.award n_aw
LEFT JOIN (SELECT award_x_mes.id_award,
award_x_mes.title_award,
COUNT(1) AS count_aw,
award_x_mes.category_award
FROM (SELECT p.*,
na.title_award,
na.award_points,
CASE
WHEN na.award_points = 1 THEN 'POSITIVE'
WHEN na.award_points = -1 THEN 'NEGATIVE'
END AS category_award
FROM (SELECT (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 JSONB_ARRAY_ELEMENTS(json_awards) AS unw
FROM notas.notas_estudiante
WHERE _id_main = 7342) t) p,
notas.award na
WHERE to_char(p.fecha_award::TIMESTAMP WITH TIME ZONE, 'MM-YYYY') = to_char('08-02-2019'::TIMESTAMP WITH TIME ZONE, 'MM-YYYY')
AND p.id_award = na.id_award) award_x_mes
GROUP BY award_x_mes.id_award,award_x_mes.title_award, award_x_mes.category_award) res_aw ON (n_aw.id_award = res_aw.id_award))
SELECT am.id_award,
am.title_award,
ROUND(am.count_awards*100 / total_awards.sum , 2) as percent_award,
am.category_award,
am.count_awards::INTEGER as number_award
FROM award_mes am,
(SELECT SUM(count_awards) FROM award_mes) total_awards
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.