CODEWITHSUNDEEP
sql-server
Andrew
Andrew

Reputation: 65

Cannot Perform Aggregate Function

I am getting the following error when I try and run the code, "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SELECT dbo.COL_TBL_WAGES.MANUAL, dbo.COL_TBL_VCOURSE.TNG_MDA_TYP_CD, 
    dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR, 
    COUNT(dbo.COL_2010_TRN_RESULTS2.EMP_TNG_STT_DT) AS CountOfEMP_TNG_STT_DT,
    dbo.COL_MASTER_COURSE_LIST.Length, 
    COUNT(
        COUNT(dbo.COL_2010_TRN_RESULTS2.EMP_TNG_STT_DT) * (
            CASE WHEN IsNumeric([COL_TBL_VCOURSE].[LENGTH]) = 1 
            THEN [COL_TBL_VCOURSE].[LENGTH] 
            ELSE 0 
            END
        )
    ) AS Total_Hours
FROM dbo.COL_TBL_WAGES INNER JOIN
    dbo.COL_2010_TRN_RESULTS2 ON dbo.COL_TBL_WAGES.[Job Group Code] =
        dbo.COL_2010_TRN_RESULTS2.JOB_GRP_CD INNER JOIN
    dbo.COL_MASTER_COURSE_LIST ON dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR = 
        dbo.COL_MASTER_COURSE_LIST.[GEMS Code] INNER JOIN
    dbo.COL_TBL_VCOURSE ON dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR = 
        dbo.COL_TBL_VCOURSE.TNG_SYS_NR
GROUP BY dbo.COL_MASTER_COURSE_LIST.Length, dbo.COL_TBL_WAGES.MANUAL,
    dbo.COL_TBL_VCOURSE.TNG_MDA_TYP_CD, 
    dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR

Upvotes: 0

Views: 154

Answers (2)

Tom
Tom

Reputation: 6663

It sounds like perhaps you are confusing COUNT() with SUM(). COUNT simply counts all non-NULL values. SUM adds whatever numeric value you supply.

For example, if you peformed COUNT and SUM on the following values you would get the following results.

VAL
100
50
25
0

SUM(VAL) = 175
COUNT(VAL) = 4

This is what I think you are really shooting for:

SUM(
        CASE WHEN IsNumeric([COL_TBL_VCOURSE].[LENGTH]) = 1 
        THEN 1
        ELSE 0 
        END
    ) AS Total_Hours

Upvotes: 0

Taryn
Taryn

Reputation: 247670

You cannot nest aggregate functions like that. You would have to use a subquery to get the result:

select MANUAL,
  TNG_MDA_TYP_CD,
  TNG_SYS_NR,
  CountOfEMP_TNG_STT_DT,
  Length,
  count(Total_Hours) Total_Hours,
from
(
  SELECT dbo.COL_TBL_WAGES.MANUAL, 
    dbo.COL_TBL_VCOURSE.TNG_MDA_TYP_CD, 
    dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR, 
    COUNT(dbo.COL_2010_TRN_RESULTS2.EMP_TNG_STT_DT) AS CountOfEMP_TNG_STT_DT, 
    dbo.COL_MASTER_COURSE_LIST.Length, 
    COUNT(dbo.COL_2010_TRN_RESULTS2.EMP_TNG_STT_DT) 
            * (CASE WHEN IsNumeric([COL_TBL_VCOURSE].[LENGTH]) = 1 
               THEN [COL_TBL_VCOURSE].[LENGTH] 
               ELSE 0 END) AS Total_Hours
  FROM dbo.COL_TBL_WAGES 
  INNER JOIN dbo.COL_2010_TRN_RESULTS2 
    ON dbo.COL_TBL_WAGES.[Job Group Code] = dbo.COL_2010_TRN_RESULTS2.JOB_GRP_CD 
  INNER JOIN dbo.COL_MASTER_COURSE_LIST 
    ON dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR = dbo.COL_MASTER_COURSE_LIST.[GEMS Code] 
  INNER JOIN dbo.COL_TBL_VCOURSE 
    ON dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR = dbo.COL_TBL_VCOURSE.TNG_SYS_NR
  GROUP BY dbo.COL_MASTER_COURSE_LIST.Length, 
    dbo.COL_TBL_WAGES.MANUAL, 
    dbo.COL_TBL_VCOURSE.TNG_MDA_TYP_CD, 
    dbo.COL_2010_TRN_RESULTS2.TNG_SYS_NR
) src
group by MANUAL,
  TNG_MDA_TYP_CD,
  TNG_SYS_NR,
  CountOfEMP_TNG_STT_DT,
  Length

Without knowing what your intention is with the query, you might need to replace the outer count() with sum()

Upvotes: 2

Related Questions