Напредни извештаи од базата (SQL и складирани процедури)

Извештај за секој салон со бројот на вработени, термини,резервации

{{{SELECT DISTINCT

fc.id AS fitnes_centar_id, br_instruktori.broj_instruktori, br_termini.broj_termini, br_rezervacii.broj_rezervacii

FROM Fitnes_Centar AS fc

LEFT JOIN (

SELECT id_gym, COUNT(user_id_instruktor) AS broj_instruktori FROM Fitnes_Instruktor GROUP BY id_gym

) AS br_instruktori ON fc.id = br_instruktori.id_gym

LEFT JOIN (

SELECT fi.id_gym, COUNT(t.termin_id) AS broj_termini FROM Fitnes_Instruktor AS fi JOIN Termin AS t ON fi.user_id_instruktor = t.instruktor_id GROUP BY fi.id_gym

) AS br_termini ON fc.id = br_termini.id_gym

LEFT JOIN (

SELECT fi.id_gym, COUNT(r.rez_id) AS broj_rezervacii FROM Fitnes_Instruktor AS fi JOIN Termin AS t ON fi.user_id_instruktor = t.instruktor_id JOIN Rezervacija AS r ON t.termin_id = r.termin_id GROUP BY fi.id_gym

) AS br_rezervacii ON fc.id = br_rezervacii.id_gym;

}}}