|
|
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
Navigation:
[Reply to this message]
|