SQL query help

I have 2 tables

JOBS

JOBNO HRSWORKED
111 12
111 1
111 3

222 4
222 6

JOBLT

JOBNO BUDGETEDHRS
111 20
222 10

SELECT JOBNO, BUDGETEDHRS, (SELECT SUM (HRSWORKED) FROM JOBS WHERE JOBS.JOBNO= JOBLT.JOBNO GROUP BY JOBS.JOBNO ) AS TOTALHRSWORKED , (TOTALHRSWORKED-BUDGETEDHRS) AS OVERBUDGET
FROM JOBLT ORDER BY JOBNO

Here I am getting the error TOTALHRSWORKED as invalid column

How can I fix that?
thx

I'm pretty sure you can't refer to an aliased column like you would a column name.

But i will be interested to see if someone else has a suggestion on how to do this kind of thing.

Code:

---

SELECT JOBNO
  , BUDGETEDHRS
  , (SELECT SUM (HRSWORKED) FROM JOBS WHERE JOBS.JOBNO=
          JOBLT.JOBNO GROUP BY JOBS.JOBNO ) AS TOTALHRSWORKED
  , ((SELECT SUM (HRSWORKED) FROM JOBS WHERE JOBS.JOBNO=
          JOBLT.JOBNO GROUP BY JOBS.JOBNO ) -BUDGETEDHRS) AS OVERBUDGET
FROM JOBLT ORDER BY JOBNO

---

Simply repeat the sub-query in the other column - it won't run it "twice" if it's MS SQL SERVER (not sure of other DB's query optimizers)...

Yeah i was worried that it would run it twice.

Thanks.