while writing a query that seemed straight forward to me, i got the error : "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
the issue perhaps wasn't the logic of what i was trying to accomplish, but just the order and placement of operations.
the solution? you can always collect your data into a temp table and then work with that. but you don't have to...
the key is select statements return result sets. right. but select statements actually operate on result sets. this doesn't seem obvious given the syntax, usually the FROM clause just references a table - perhaps with some kind of join. but it doesn't have to... trick is, that data set is actually just a result set, and any result set works - even another SELECT statement. meaning you can compose expressions that operate on other select statements (commonly referred to a derived table).
since you can operate directly on a result set you can forego the formality of using a temp table. meaning you can having your aggregate or subquery and eat your data too... err perform an aggregate function.
here's a dry, simple example showing the form, a select from a result set, with an aggregate function thrown in :
SELECT MyColumn
FROM (
SELECT 1 as MyColumn
) as mytable
GROUP BY MyColumn
note: you *have* to name the composed columns and tables.
ok, a more sophisticated example. this one is performing aggregate function on an aggregate function, on a subset of data in a table.
SELECT Zone, Club, SUM(CASE when ClientCount <=1 then 1 else 0 end) as '1', SUM(CASE when ClientCount =2 then 1 else 0 end) as '2', SUM(CASE when ClientCount =3 then 1 else 0 end) as '3', SUM(CASE when ClientCount =4 then 1 else 0 end) as '4', SUM(CASE when ClientCount >4 then 1 else 0 end) as '>4' FROM ( SELECT s.SourceID as Zone, s.SourceDesc as Club, count(a.ClientID) as ClientCount from tblAssessment a, tblSource s where a.SourceID LIKE 'Kiosk%' and s.SourceID=a.SourceID GROUP BY s.SourceID, s.SourceDesc , a.ClientID ) as x GROUP BY Zone, Club ORDER BY Zone
another real world solution, this example lets you return the unique row identifiers after using the fields that determine duplicates entries - in a simple, well single elegant statement.
Finding Duplicates. If we group records together by certain identifying fields we can then use a Count function to extract those that are duplicated. This query utilizes a sub-query and, based on their first and last names, returns a list of DelegateIDs for those delegates that have duplicate records.
SELECT Delegate.DelegateID, Delegate.FirstName, Delegate.LastName
FROM Delegate INNER JOIN
(SELECT FirstName, LastName
FROM Delegate
GROUP BY FirstName, LastName
HAVING Count(DelegateID) > 1
) AS Duplicates
ON Delegate.FirstName = Duplicates.FirstName
AND Delegate.LastName = Duplicates.LastName
it doesn't stop there. conditionals also use result sets. here's an example :
SELECT * FROM Delegate
WHERE DelegateID int
(SELECT DelegateID FROM DelegateRegistration
WHERE RegDate < '05/14/2000')
you can even use them as qualifiers in the conditionals for UPDATE and DELETES.
Removing Duplicates. The following query removes all duplicates from the delegate table, leaving only the originals of the duplicated record. Here, we define a record as having a duplicate if there is another record with matching FirstName and LastName fields. This query relies on a view being set up that returns a list of duplicate delegates. The view is called DupDels.
DELETE FROM Delegate
WHERE DelegateID =
ANY ( SELECT DupDels.DelegateID
FROM DupDels LEFT JOIN
(SELECT Min(DelegateID) AS DelegateID, FirstName, LastName
FROM DupDels GROUP BY FirstName, LastName) AS FirstDup
ON DupDels.DelegateID = FirstDup.DelegateID
WHERE FirstDup.DelegateID IS NULL
)
take special note how this works below since only *one* row can be updated.
The UPDATE statement can include a FROM clause allowing you to retrieve fields referenced by the WHERE clause of the SET clause. This is useful if you wish to update records in one table based on the values of an associated record in a second. This is illustrated in the following example:
UPDATE Del
SET Del.Status = RegType.DisplayName
FROM (Delegate Del INNER JOIN DelegateRegistration DelReg
ON Del.DelegateID = DelReg.DelegateID)
INNER JOIN RegistrationType RegType
ON DelReg.RegistrationTypeID = RegType.RegistrationTypeID
WHERE RegType.RegistrationTypeID = 2
Since a table row can only be updated with one record, if the FROM clause returns more than one, then the first record to match the criteria is used. Ideally, the FROM clause should only return one record for each row.
the FROM clause.:
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
| user_defined_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
so, likewise you can also use views, rowset functions, and user defined fuctions in a similar manner. (of course!)
Check out:
http://www.devguru.com/features/tutorials/AdvancedSQL/advancedSQL.html
http://www.sqlteam.com/item.asp?ItemID=9867