Fórum studentů MFF UK

InvInv píše:Na SQL serveru SELECT FROM SELECT podle všecho nejde:

Kód: Vybrat vše

SELECT AVG(suma)
  FROM (
    SELECT rc_ucitele, SUM(hodin) AS suma
      FROM Rozvrh NATURAL JOIN Ucitel
      WHERE katedra = 'KSI'
      GROUP BY rc_ucitele
  ) 

ale je potřeba napsat to takhle:

Kód: Vybrat vše

WITH uci AS
(
      SELECT rc_ucitele, SUM(hodin) AS suma
      FROM Rozvrh NATURAL JOIN Ucitel
      WHERE katedra = 'KSI'
      GROUP BY rc_ucitele
)
SELECT AVG(suma)
FROM uci

Kód: Vybrat vše

SELECT AVG(suma)
  FROM (
    SELECT rc_ucitele, SUM(hodin) AS suma
      FROM Rozvrh NATURAL JOIN Ucitel
      WHERE katedra = 'KSI'
      GROUP BY rc_ucitele
  ) 

WITH uci AS
(
      SELECT rc_ucitele, SUM(hodin) AS suma
      FROM Rozvrh NATURAL JOIN Ucitel
      WHERE katedra = 'KSI'
      GROUP BY rc_ucitele
)
SELECT AVG(suma)
FROM uci

Myshaak píše:Diky za pokus! Ale asi jsi me spatne pochopil, jeden ucitel muze ucit vic premetu, nejlepsi asi bude priklad:

<b>Ucitel:</b> (rc_uc, jm_uc, katedra)
rc1, "Pavel", "KSI"
rc2, "Pepa", "KSI"
rc3, "Jaja", "KAM"

<b>Rozvrh:</b> (kod, rc_uc,hodin)
001, rc1, 3
002, rc2, 1
003, rc2, 2
007, rc3, 3

...pokud nejsem uz uplne blby, tak by Tvuj dotaz vratil 2, pricemz spravna logicka odpoved je 3 (jak Pavel tak Pepa uci kazdy celkem 3 hodiny)

SELECT AVG(suma)
  FROM (
    SELECT rc_ucitele, SUM(hodin) AS suma
      FROM Rozvrh NATURAL JOIN Ucitel
      WHERE katedra = 'KSI'
      GROUP BY rc_ucitele
  )