Changes between Version 7 and Version 8 of AdvancedReports

Timestamp:

01/05/23 06:32:23 (2 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ -225 +225 @@
 group by e.id_event 
 }}}
+
+=== Извештај за секој филм, колку карти се продадени и колкав профит има секој филм направено, колку различни клиенти купиле карта за филмот и колку рејтинзи има секој филм ===
+{{{#!sql
+select f.id_film,
+coalesce((
+select count(distinct t.id_ticket) from tickets t
+left join projections p on p.id_projection=t.id_projection 
+left join films f1 on f1.id_film=p.id_film 
+where f.id_film=f1.id_film 
+),0),
+coalesce((
+select sum(t.price) from tickets t
+left join projections p on p.id_projection=t.id_projection 
+left join films f1 on f1.id_film=p.id_film 
+where f.id_film=f1.id_film 
+),0),
+coalesce((
+select count(distinct c.id_customer) from customers c
+left join tickets t2 on t2.id_customer=c.id_customer 
+left join projections p on p.id_projection=t2.id_projection 
+left join films f1 on f1.id_film=p.id_film 
+where f.id_film=f1.id_film 
+),0),
+coalesce((
+select count(crf.rating) from customer_rates_film crf 
+left join films f2 on f2.id_film=crf.id_film 
+where f2.id_film=f.id_film
+),0)
+from films f 
+}}}