SQL query help

[nilaavu]

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

[space_monkey]

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.

[szlamany]

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)...

[space_monkey]

Yeah i was worried that it would run it twice.

[nilaavu]

Thanks.