para obtener los premios de un salon por mes con porcentajes

WITH award_mes AS (SELECT n_aw.id_award, n_aw.title_award, COALESCE(res_aw.count_aw, 0) AS count_awards, COALESCE(res_aw.category_award, 'EMPTY') AS category_award FROM notas.award n_aw LEFT JOIN (SELECT award_x_mes.id_award, award_x_mes.title_award, COUNT(award_x_mes.award_points) 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) ORDER BY n_aw.id_award ASC) SELECT am.id_award, am.title_award, ROUND(am.count_awards*100 / total_awards.tot_sum , 2) as percent_award, ROUND(total_awards.pos_sum*100 / total_awards.tot_sum , 2) as percent_pos, ROUND(total_awards.neg_sum*100 / total_awards.tot_sum , 2) as percent_neg, am.category_award, am.count_awards::INTEGER as number_award, total_awards.tot_sum, total_awards.pos_sum, total_awards.neg_sum FROM award_mes am, (SELECT SUM(count_awards) AS tot_sum, SUM(CASE WHEN category_award = 'POSITIVE' THEN count_awards ELSE 0 END)::INTEGER AS pos_sum, SUM(CASE WHEN category_award = 'NEGATIVE' THEN count_awards ELSE 0 END)::INTEGER AS neg_sum FROM award_mes) total_awards --) tot

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.