USE Poliklinika;

SELECT 
	L.spec, 
    N.poplatok, 
    SUM(N.poplatok) suma
FROM Lekari L
JOIN Navstevy N ON L.idL = N.idL
WHERE N.poplatok IN (500,200,800)
GROUP BY L.spec, N.poplatok;

SELECT 
    L.spec, 
    N.poplatok, 
    SUM(N.poplatok) suma
FROM Lekari L 
JOIN Navstevy N ON L.idL = N.idL
WHERE N.poplatok IN (500,200,800)
GROUP BY spec, poplatok WITH ROLLUP
ORDER BY spec, poplatok DESC;

SELECT 
    CASE 
        WHEN L.spec IS NULL THEN 'Vsetci'
        ELSE L.Spec 
    END spec,
    CASE 
        WHEN N.poplatok IS NULL THEN 'SumPop'
        ELSE CAST(N.poplatok AS VARCHAR(10)) 
    END poplatok,
    SUM(N.poplatok) suma
FROM Lekari L JOIN Navstevy N ON L.idL = N.idL
WHERE N.poplatok IN (500,200,800)
GROUP BY spec, poplatok WITH ROLLUP
ORDER BY spec, poplatok DESC;

SELECT 
    CASE 
        WHEN L.spec IS NULL THEN 'Vsetci'
        ELSE L.Spec 
    END spec,
    CASE 
        WHEN N.poplatok IS NULL THEN 'SumPop'
        ELSE CAST(N.poplatok AS VARCHAR(10)) 
    END poplatok,
    SUM(N.poplatok) suma
FROM Lekari L JOIN Navstevy N ON L.idL = N.idL
WHERE N.poplatok IN (500,200,800)
GROUP BY spec, poplatok WITH CUBE
ORDER BY spec, poplatok DESC;