SQL with calculation based on multiple tables

I have a simple DB with the following two tab开发者_StackOverflowles.

There is a one-to-many relationship between the id field in the sessions table and the session_id field in the candidates table.

I'd like a query that will SELECT * FROM SESSIONS.

SESSIONS (TABLE)

|| id || title || max_candidates || description ||
|| 01 || fish  || 05             || some string ||
|| 02 || birds || 10             || some string ||

CANDIDATES (TABLE)

|| session_id || user_id ||
|| 01         || user01  ||
|| 02         || user12  ||
|| 02         || user03  ||
|| 02         || user05  ||

However, in addition to returning the information from the sessions table, I'd also like it to return a calculated column named avaliable_spaces.

I'd like to have this column return the number of avalaible spaces for a particular session based on (no of times session_id occurs in CANDIDATES) - (max_candidates)

In the above example it would return (obviously minus the column headders);

|| id || title || max_candidates || description || avaliable_spaces ||

|| 01 || fish  || 05             || some string ||04                ||
|| 02 || birds || 10             || some string ||07                ||

Does this make sense? And if so, is it even possible!? (as you can probably guess) I'm an SQL noob and this is well beyond me!

---

SELECT S.id,
       S.title,
       S.max_candidates,
       S.description,
       S.max_candidates - COUNT(c.user_id) as available_spaces
FROM   SESSIONS S
       LEFT OUTER JOIN CANDIDATES C
         ON C.session_id = s.id
GROUP  BY S.id,
          S.title,
          S.max_candidates,
          S.description  

---

SELECT
    S.*, S.max_candidates - C.Filled AS avaliable_spaces
FROM
    SESSIONS S
    LEFT JOIN
    (
    SELECT session_id, COUNT(*) AS Filled FROM CANDIDATES GROUP BY session_id
    ) C ON S.ID = C.session_id