Posted by Paul on 08/24/07 18:26

Hello All,

I am having trouble coming up with the correct SQL to accomplish a
task. Most of the SQL I use is quite simple and I rarely have to
resort to subqueries so I don't have a lot of experience with them.

The following SQL gives me the result set that I want.

SELECT
ent.colDate,
(SELECT sum(colTimeSpent) FROM tblEntryActivity act WHERE
ent.colEntryID = act.colEntryID) AS [Indirect Service Time],
(SELECT sum(colTimeSpent) FROM tblEntryService ser WHERE
ent.colEntryID = ser.colEntryID) AS [Direct Service Time],
ent.colTravelTime
FROM
tblEntry ent, tblEmployee emp, tblGeneralNote g, tblClient c
WHERE
ent.colEmployeeID = emp.colEmployeeID
AND ent.colCaseNoteID = g.colCaseNoteID
AND g.colClientID = c.colClientID
AND ent.colDate > DATEADD(month, -1, GETDATE())
AND ent.colDate <= GETDATE()
AND emp.colEmployeeID = 87
ORDER BY
ent.colDate

So far so good. But what I want to do next is take the sum of the last
3 columns and group them by ent.colDate. Here is the SQL that I have
tried to execute. Obviously the code is wrong, but I am pasting it
here because I think it should be obvious what I am TRYING to
accomplish.

SELECT
ent.colDate,
SUM((SELECT sum(colTimeSpent) FROM tblEntryActivity act WHERE
ent.colEntryID = act.colEntryID)) AS [Indirect Service Time],
SUM((SELECT sum(colTimeSpent) FROM tblEntryService ser WHERE
ent.colEntryID = ser.colEntryID)) AS [Direct Service Time],
SUM(ent.colTravelTime)
FROM
tblEntry ent, tblEmployee emp, tblGeneralNote g, tblClient c
WHERE
ent.colEmployeeID = emp.colEmployeeID
AND ent.colCaseNoteID = g.colCaseNoteID
AND g.colClientID = c.colClientID
AND ent.colDate > DATEADD(month, -1, GETDATE())
AND ent.colDate <= GETDATE()
AND emp.colEmployeeID = 87
GROUP BY
ent.colDate
ORDER BY
ent.colDate


Here is the error message that I am getting.

Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.

I am hoping that someone out there can give me a clue as to how I can
get the result I want without trying to "perform and aggregate
function on an expression containing a subquery".

Help is always appreciated. Thanks in advance.

Paul

[Back to original message]