Changes between Version 26 and Version 27 of AdvancedReports

Timestamp:

12/23/22 22:19:59 (3 years ago)

Author:

191285

Comment:

--

AdvancedReports

@@ -5 +5 @@
 {{{#!div style="font-size: 90%"
   {{{#!sql 
-select q4.god as godina, p.ime as predmet, q4.broj_casovi as broj_casovi
-from
-(
-
-    select * from 
-     (
-         select q1.godina as god, max(q1.broj_casovi) as maksimum from
-             (
-              select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
-                from predmeti p
-                join casovi c on c.id_predmet = p.id_predmet 
-                group by godina,predmet
-                ) q1
-                group by godina
-        ) q2
-        join 
-        (
-        select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
-                from predmeti p
-                join casovi c on c.id_predmet = p.id_predmet 
-                group by godina,predmet
-        ) q3
-        on q2.god=q3.godina and q2.maksimum=q3.broj_casovi
-
-) q4
-join predmeti p on p.id_predmet = q4.predmet
-order by godina desc;
+select q4."location", q4.max_broj_nastani
+from (
+              (select extract (year from t.date) as godina,  count(distinct e.id_event) broj_nastani, t."location" 
+                from ticket t 
+                join events e  on t.id_event =e.id_event 
+                group by godina,  t."location" 
+                
+               ) q1
+                
+                join 
+                (
+                select max(q2.broj_nastani) as max_broj_nastani
+                from (
+                select extract (year from t.date) as godina,  count(distinct e.id_event) broj_nastani, t."location" 
+                from ticket t 
+                join events e  on t.id_event =e.id_event 
+                group by godina,  t."location"
+                ) q2
+                ) q3
+       on q1.broj_nastani=q3.max_broj_nastani) q4
   }}}
 }}}