Subqueries and Aggregate Functions

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

.

Relevant Pages

  • Re: SQL Queries Questions
    ... "Seikyo" wrote in message ... > I use several SQL to do the stuff and Macro to link them I think. ... > will be a big trouble when the database get larger. ... Almost certainly there is a simpler way to accomplish what you want ... ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query
    ... What does the pound sign ... accomplish in sql? ... select * from tblYourTableHere ...
    (comp.databases.ms-access)
  • Re: Securing the MDF file
    ... Some folks have used EFS to accomplish this. ... both successfully, and with dismal failures. ... The successes where with ... With SQL 2005, there are many excellent internal encryption capabilities ...
    (microsoft.public.sqlserver.security)
  • Re: Group By - SQL Question?
    ... I WOULD LIKE THE SQL RESULTS TO LOOK LIKE THIS: ... How can I accomplish this - I am most concerned in ... RBrandt at Hunter dot com ...
    (microsoft.public.access.queries)