sql server programming: Query Fails -- sql server programming

Query Fails

qAnand
8/17/2004 10:53:23 PM

sql server programming:

Hi All,
I have a this query which works fine,

SELECT Hours *
(SELECT Rate FROM tbRate WHERE ID = (SELECT MAX(ID) FROM
tbRate WHERE Project=T.Project AND Employee=T.Employee))
as Amt
FROM EmployeeTime T

The output is like this:
Amt
-------------
200.0
300.0

If I want to sum the two amount, the query fails.

Query I wrote is :

SELECT SUM(Hours *
(SELECT Rate FROM tbRate WHERE ID = (SELECT MAX(ID) FROM
tbRate WHERE Project=T.Project AND Employee=T.Employee)))
as TotalAmt
FROM EmployeeTime T

This fails by saying:
"Cannot perform an aggregate function on an expression
containing an aggregate or a subquery."

Can anyone help me in accomplish this task.

Thanks in advance
qAnand

RE: Query Fails

John Bell
8/17/2004 11:45:02 PM

Hi

You could try

SELECT SUM(Amt) as Total
FROM
( SELECT Hours *
(SELECT Rate FROM tbRate WHERE ID = (SELECT MAX(ID) FROM
tbRate WHERE Project=T.Project AND Employee=T.Employee))
as Amt
FROM EmployeeTime T ) T

It looks like you are trying to do the work of a report writer, so maybe
you should do this in the front end and not the database?

John

[quoted text, click to view]

Re: Query Fails

Uri Dimant
8/18/2004 9:40:49 AM

qAnand
SELECT SUM(Amt) FROM
(
SELECT Hours *
(SELECT Rate FROM tbRate WHERE ID = (SELECT MAX(ID) FROM
tbRate WHERE Project=T.Project AND Employee=T.Employee))
as Amt
FROM EmployeeTime T
) AS D

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.