WITH smerovi AS(
    SELECT d.id_smera, ug.godina, COUNT(*) AS br_studenata
    FROM dosije d JOIN upis_godine ug ON d.indeks = ug.indeks
    WHERE id_smera IN(SELECT id_smera FROM smer WHERE id_nivoa IN(
                    SELECT id_nivoa FROM nivo_kvalifikacije WHERE naziv LIKE 'Osnovne%'))
    AND EXISTS(SELECT * FROM STATUS s JOIN dosije d1 ON d1.indeks = s.indeks
                WHERE d1.id_smera = d.id_smera
                AND YEAR(s.datum) = ug.godina
                AND s.STATUS = 'ispisan')
    GROUP BY d.id_smera, ug.godina
    HAVING COUNT(*) > 20),
broj_svih AS(SELECT d.id_smera, COUNT (*) AS br_svih
    FROM dosije d
    WHERE NOT EXISTS(
        SELECT * FROM upisan_kurs uk
        WHERE uk.indeks = d.indeks
        AND uk.godina = d.indeks/10000
        AND NOT EXISTS(SELECT * FROM ispit i
            WHERE indeks = uk.indeks
            AND i.ocena > 5 AND i.status_prijave = 'o'
            AND i.id_predmeta = uk.id_predmeta))
    GROUP BY d.id_smera)
SELECT s.naziv, d.indeks/10000 AS godina, COUNT(DISTINCT d.indeks) AS br_upisanih, bs.br_svih
FROM smer s JOIN smerovi ss ON s.id_smera = ss.id_smera JOIN dosije d ON d.id_smera = s.id_smera
JOIN broj_svih bs ON bs.id_smera = ss.id_smera
GROUP BY s.id_smera, s.naziv, d.indeks/10000, bs.br_svih;