Changes between Version 51 and Version 52 of AdvancedReports

Timestamp:

12/24/22 22:16:14 (3 years ago)

Author:

181006

Comment:

--

AdvancedReports

@@ -100 +100 @@
   {{{#!sql 
 select e2."name", e2."date", e2.city, e2.price, q4.max_broj_zainteresirani
-from    (
+from (
 (
         select e.id_event, count(u.id_user) as broj_na_zainteresirani_kupuvaci
@@ -137 +137 @@
   {{{#!sql 
 select q4."location", q4.max_broj_nastani
-from    (
+from (
 (
         select extract (year from t.date) as godina,
@@ -170 +170 @@
   {{{#!sql 
 select q4.godina, q4.city, q4.max_broj_nastani
-from    (
+from (
 (
         select extract (year from e."date") as godina,
@@ -200 +200 @@
   {{{#!sql 
 select q4."name", q4.surname, q4.max_broj_nastani
-from    (
+from (
 (
         select u."name", u.surname, count(e.id_event) as broj_nastani
@@ -226 +226 @@
 }}}
 
+== Настан со максимален број на продадени карти
+
+{{{#!div style="font-size: 90%"
+  {{{#!sql
+select q5.id_event, q5."name", q5.max_prodadeni_karti
+from (
+(
+        select q1.id_event, q1."name",
+                case
+                        when q1.prodadena_karta >= 1 then q1.prodadena_karta
+                        else 0
+                end as prodadeni_karti
+        from (
+                select e.id_event, e."name", count(t2.id_invoice) as prodadena_karta
+                from ticket t
+                join events e on t.id_event = e.id_event
+                join transactions t2 on t.id_cart = t2.id_cart
+                group by e.id_event, e."name"
+) q1 ) q3
+
+join
+
+(
+        select max(q2.prodadeni_karti) as max_prodadeni_karti
+        from (
+                select q1.id_event, q1."name",
+                        case
+                                when q1.prodadena_karta >= 1 then q1.prodadena_karta
+                                else 0
+                        end as prodadeni_karti
+                from (
+                        select e.id_event, e."name", count(t2.id_invoice) as prodadena_karta
+                        from ticket t
+                        join events e on t.id_event = e.id_event
+                        join transactions t2 on t.id_cart = t2.id_cart
+                        group by e.id_event, e."name"
+) q1 ) q2
+
+) q4
+on
+        q3.prodadeni_karti = q4.max_prodadeni_karti )
+as q5
+  }}}
+}}}
+
 == Вкупно продадени карти за секој настан