-- posiciones

select ( select nombre from "CertamenTabasco_municipio" where id in (select municipio_id from "CertamenTabasco_participante" where id = C.participante_id)), sum(C.calificacion)
from public."CertamenTabasco_calificacion" C
where ronda_id = 1
group by C.participante_id
order by 2 desc;


SELECT 
"P".ronda_id,
  "M".nombre, sum("C".calificacion) 
FROM 
  public."CertamenTabasco_calificacion" "C", 
  public."CertamenTabasco_municipio" "M", 
  public."CertamenTabasco_participante" "P"
WHERE 
  "P".id = "C".participante_id AND
  "P".municipio_id = "M".id

  --and "C".ronda_id = 1
group by "M".id, "P".ronda_id
order by 1 desc, 3 desc;




--select * from "CertamenTabasco_calificacion" where id = 2410

--Calificacion.objects.filter(evento = juez.evento, ronda = ronda).count()

select count(*)
FROM public."CertamenTabasco_calificacion" "C"
where evento_id=3 and ronda_id=1
;
--juecesall.count() * participantesporronda.count() * criterios.count()
select 
(select count(*) from "CertamenTabasco_juez" where activo = True) as "Total Jueces",
(select count(*) from "CertamenTabasco_participante" where ronda_id = 1 ) as "Prticipantes",
(select count(*) from "CertamenTabasco_criterio") as "Criterios",

(select count(*) from "CertamenTabasco_juez" where activo = True)*
(select count(*) from "CertamenTabasco_participante" where ronda_id = 1 )* 
(select count(*) from "CertamenTabasco_criterio") as "Total";
 
-- MONITOREO DE VOTOS
select 
(SELECT username FROM auth_user where id = juez_id) as juez,

juez_id, ronda_id, count(*) from "CertamenTabasco_calificacion" group by juez_id, ronda_id;
select "l".username, "l".id, "r".ronda_id, count("r".ronda_id) as total
from auth_user "l" left join "CertamenTabasco_calificacion" "r" on l.id = r.juez_id
where l.is_active = true
and l.is_staff = false
--and ronda_id = 1
and evento_id = 3
group by r.juez_id, r.ronda_id, l.username, l.id
order by 3, 2, 4 desc ;