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

Re: Subqueries and Aggregate Functions

Paul (heythereto@yahoo.ca) writes:
> 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.

Illegal and made-up syntax is rarely obvious. This may be what you
are looking for:

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

I first rewrote the query into JOIN syntax, as I find this syntax
easier to read, not the least when I work with derived tables. This
syntax is also required when you work with outer joins in SQL 2005
or later.

I then introduced two derived tables. A derived tables is logically
a temp table within the query, but it is not materialised, and the
optimizer can recast the actual computation order, so if these
Activity tables are huge, it is not likely that SQL Server will
compute the sum for all entry ids, but only for those that are
determined by the where clause.

Note here that I don't know your tables and keys, so the above is a
bit of guesswork. If the query does not cut it for you, please post

o CREATE TABLE statements for your tables, preferrably simplified.
o INSERT statements with sample data.
o The desired result given the sample.

Here is a second version of the query:

SELECT ent.Date,
SUM(act.TimeSpent) AS [Indirect Service Time],
SUM(ser.TimeSpent) [Direct Service Time],
SUM(ent.TravelTime)
FROM Entry ent
JOIN Employee emp ON ent.EmployeeID = emp.EmployeeID
JOIN GeneralNote g ON ent.CaseNoteID = g.CaseNoteID
JOIN Client c ON g.ClientID = c.ClientID
JOIN (SELECT EntryID, SUM(TimeSpent) AS TimeSpent
FROM EntryActivity
GROUP BY EntryID) AS act ON ent.EntryID = act.EntryID
JOIN (SELECT EntryID, SUM(TimeSpent) AS TimeSpent
FROM EntryService
GROUP BY EntryID) AS ser ON ent.EntryID = ser.EntryID
WHERE ent.Date > DATEADD(month, -1, GETDATE())
AND ent.Date <= GETDATE()
AND emp.EmployeeID = 87
GROUP BY ent.Date
ORDER BY ent.Date

I just wanted to show how much clearer and concise the query get
without those redundant col and tbl prefixes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Subqueries and Aggregate Functions

Thanks very much Erland for taking the time to rewrite my query. I
learned a lot from your help.

Paul