Using SUM with a subquery

Tech-Archive recommends: Fix windows errors by optimizing your registry

the following query returns the error:
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.

Any ideas how to make it work? It's the second SUJ causing the error
and i can see why but can't work out a solution.

thanks in advance

dan

SELECT SUM(CO2 * (NormalMileage *2)), tbluser.userid,SUM((SELECT CO2
FROM tblTravelMode WHERE ModeCode = tblUser.TravelModeCode))

FROM tblTravelDiary
INNER JOIN tblTravelMode
ON tblTravelDiary.TravelModeCode = tblTravelMode.ModeCode
INNER JOIN tblUser ON
tblTravelDiary.UserId = tblUser.UserId
GROUP BY tblUser.UserId

.

Relevant Pages

  • Re: Using SUM with a subquery
    ... Cannot perform an aggregate function on an expression containing an ... FROM tblTravelMode WHERE ModeCode = tblUser.TravelModeCode)) ... INNER JOIN tblUser ON ...
    (microsoft.public.sqlserver)
  • Re: Using SUM with a subquery
    ... Cannot perform an aggregate function on an expression containing an ... FROM tblTravelMode WHERE ModeCode = tblUser.TravelModeCode)) ... INNER JOIN tblUser ON ...
    (microsoft.public.sqlserver)
  • Re: Remove Duplicate Rows
    ... Data ON DataHeader.DataID = Data.DataID INNER JOIN ... contained in either an aggregate function or the GROUP BY clause. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Troubles.
    ... Your simple Select query has an aggregate function in it - ... FROM Customers INNER JOIN (Orders INNER JOIN (Products INNER JOIN ...
    (microsoft.public.access.queries)
  • Re: Finding the maximum number from a count function
    ... Then you would need a subquery as you mentioned. ... University of Maryland Baltimore County ... suppose I needed records whose count ... I would think there is a way to use an aggregate function ...
    (microsoft.public.access.gettingstarted)