| 76 | | |
| | 76 | == Извештај за вкупно продадени карти по жанрови |
| | 77 | {{{#!sql |
| | 78 | select g.genre_id,g.genre_name, |
| | 79 | case when tabela.prodadena_karta >=1 then tabela.prodadena_karta else 0 end as prodadeni_karti |
| | 80 | from genre as g |
| | 81 | join is_ as is1 on g.genre_id=is1.genre_id |
| | 82 | join movie as m on is1.movie_id=m.movie_id |
| | 83 | join movieprojection as mp on m.movie_id=mp.movie_id |
| | 84 | join reservation as r on mp.projection_id=r.reservation_id |
| | 85 | join ( |
| | 86 | select g2.genre_id,g2.genre_name,count(p1.payment_id) as prodadena_karta |
| | 87 | from genre as g2 |
| | 88 | join is_ as is2 on g2.genre_id=is2.genre_id |
| | 89 | join movie as m2 on is2.movie_id=m2.movie_id |
| | 90 | join movieprojection as mp2 on m2.movie_id=mp2.movie_id |
| | 91 | join reservation as r2 on mp2.projection_id=r2.reservation_id |
| | 92 | join payment as p1 on r2.reservation_id=p1.reservation_id |
| | 93 | group by 1,2 |
| | 94 | |
| | 95 | )as tabela on g.genre_id=tabela.genre_id |
| | 96 | }}} |
