SQL add Sum to existing query (Cannot perform an aggregate function on an expression containing an aggregate or a subquery.)

Question

I have a existing working SQL query I would like to now GroupBy but am getting the error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Explanation of my scenario:

My main table (dbo.DataLog) contains 3 columns, TimestampUTC, MeterTagId, Data.

Data typically comes in at 15 minute intervals and I have many meters (MeterTagId) for each TimestampUTC. The Data column is a float and this is a totalised value. i.e. to get the actual value for a meter period I need to subtract the last value from the current one. Before now I have successfully been querying individual meters but now I am trying to group by time and show a sum/total of all meters for that time.

Original working non summed query:

 SELECT
    l.TimestampUTC
    -- Get this value minus the last value
    ,(SELECT  (l.[Data] - 
                (   SELECT TOP 1 l2.Data
                    FROM [DataLog] l2
                    WHERE l2.MeterTagId = l.MeterTagId
                    AND l2.TimestampUTC < l.TimestampUTC
                    ORDER BY l2.TimestampUTC DESC)
                )
        ) AS Actual_Value
    FROM [dbo].[DataLog] l
    INNER JOIN [dbo].MeterTags t on t.MeterTagId = l.MeterTagId
    INNER JOIN [dbo].Meters m on m.MeterId = t.MeterId
    INNER JOIN [dbo].GroupsMeters gm on gm.MeterId = m.MeterId
    INNER JOIN [dbo].Groups g on g.GroupId = gm.GroupId
    LEFT OUTER JOIN dbo.Units u on u.UnitId = t.UnitId
    WHERE (@MeterId is null OR M.MeterId in (@MeterId))
    AND (@MeterTagId is null OR t.MeterTagId in (@MeterTagId))
    AND (@StartDate is null OR l.TimestampUTC >= @StartDate)
    AND (@EndDate is null OR l.TimestampUTC <= @EndDate)
    AND (@GroupId is null OR g.GroupId in (@GroupId))

.

My attempt to to get the summary:

 SELECT
    l.TimestampUTC
    -- Get this value minus the last value
    ,   (SELECT  SUM(l.[Data] - 
                (   SELECT TOP 1 l2.Data
                    FROM [DataLog] l2
                    WHERE l2.MeterTagId = l.MeterTagId
                    AND l2.TimestampUTC < l.TimestampUTC
                    ORDER BY l2.TimestampUTC DESC)
                )
        )AS Actual_Value
    FROM [dbo].[DataLog] l
    INNER JOIN [dbo].MeterTags t on t.MeterTagId = l.MeterTagId
    INNER JOIN [dbo].Meters m on m.MeterId = t.MeterId
    INNER JOIN [dbo].GroupsMeters gm on gm.MeterId = m.MeterId
    INNER JOIN [dbo].Groups g on g.GroupId = gm.GroupId
    LEFT OUTER JOIN dbo.Units u on u.UnitId = t.UnitId
    WHERE (@MeterId is null OR M.MeterId in (@MeterId))
    AND (@MeterTagId is null OR t.MeterTagId in (@MeterTagId))
    AND (@StartDate is null OR l.TimestampUTC >= @StartDate)
    AND (@EndDate is null OR l.TimestampUTC <= @EndDate)
    AND (@GroupId is null OR g.GroupId in (@GroupId))
    AND t.Name ='Real Energy Net'
    GROUP BY l.TimestampUTC

I have read other posts on here but can't get my head around the logic required, I imagine/hope this is something sql dev's come across regularly? Thanks!

John John · Accepted Answer · 2013-10-28T05:49:34

OK, I worked it out, it's simple really. Hopefully this explanation helps someone else with the same issue in the future.

SELECT 
    myTable.TimestampUTC
    , SUM(myTable.Actual_Value) as [Actual Value]
FROM
(
--My original query
) AS myTable

GROUP BY myTable.TimestampUTC

SQL add Sum to existing query (Cannot perform an aggregate function on an expression containing an aggregate or a subquery.)

1 Answers