CODEWITHSUNDEEP
sqlsql-server
Niar
Niar

Reputation: 540

Error with aggregate function

when i execute the below query i get the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." How can i write the same query without getting the error.

SELECT CCMID[Client],CCMName[Company],
    (SELECT SUM( (Select SUM( PAm) from PaySalReg(NOLOCK) 
    Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
    where PSRAMId=AMId and PEMDedTy ='SSC' 
    and PProYe=(SELECT DOYe FROM DefOMonth)
    and PProMo=(SELECT DOMo-1 FROM DefOMonth))
    -
    (Select SUM(PAm) from PaySalReg(NOLOCK) 
    Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
    where PSRAMId=AMId and PEMDedTy ='PRTY'
    and PProYe=(SELECT DOYe FROM DefOMonth)
    and PProMo=(SELECT DOMo-1 FROM DefOMonth)))
    from AssMas(NOLOCK) WHERE ACID = CCMID ) AS [Net Pay],
    COUNT(PAMId)[No. of Associates] 
    FROM PaySalReg
    JOIN AssMas ON AMId =PSRAMId
    JOIN CorpClMas ON ACID = CCMID
    WHERE PProMon='10'
    AND PProYe='2014'
    AND PAPPSTA IS NULL
    GROUP BY CCM_Name,CCM_ID

Upvotes: 0

Views: 377

Answers (3)

Spock
Spock

Reputation: 4910

You can remove the outer aggregate, and wrap the query in a sub query... Then perform the aggregate on the sub query... Something like this...

SELECT CCMID, CCMName, [No. of Associates], SUM([Net Pay])
FROM (
    SELECT Client as CCMID, Company as CCMName,
        (Select SUM( PAm) from PaySalReg(NOLOCK) 
        Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
        where PSRAMId=AMId and PEMDedTy ='SSC' 
        and PProYe=(SELECT DOYe FROM DefOMonth)
        and PProMo=(SELECT DOMo-1 FROM DefOMonth))
        -
        (Select SUM(PAm) 
        from PaySalReg(NOLOCK) 
        Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
        where PSRAMId=AMId and PEMDedTy ='PRTY'
        and PProYe=(SELECT DOYe FROM DefOMonth)
        and PProMo=(SELECT DOMo-1 FROM DefOMonth)) AS [Net Pay],

        COUNT(PAMId) AS [No. of Associates] 
    FROM PaySalReg
        JOIN AssMas ON AMId =PSRAMId
        JOIN CorpClMas ON ACID = CCMID
    WHERE PProMon='10'
    AND PProYe='2014'
    AND PAPPSTA IS NULL) data
GROUP BY CCMID, CCMName, [No. of Associates]

(untested, as you didn't supply a data structure.)

Upvotes: 1

asontu
asontu

Reputation: 4659

I fixed your coding-style and indentation a bit and the problem becomes visible:

SELECT
    CCMID[Client],
    CCMName[Company],
    (
        SELECT SUM(
            (   
                Select SUM(PAm) from PaySalReg(NOLOCK) 
                Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
                where PSRAMId = AMId
                    and PEMDedTy = 'SSC' 
                    and PProYe = (SELECT DOYe FROM DefOMonth)
                    and PProMo = (SELECT DOMo-1 FROM DefOMonth)
            ) - (
                Select SUM(PAm) from PaySalReg(NOLOCK) 
                Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
                where PSRAMId = AMId
                    and PEMDedTy = 'PRTY'
                    and PProYe = (SELECT DOYe FROM DefOMonth)
                    and PProMo = (SELECT DOMo-1 FROM DefOMonth)
            )
        )
        from AssMas(NOLOCK)
        WHERE ACID = CCMID
    ) AS [Net Pay],

    COUNT(PAMId) [No. of Associates] 

FROM PaySalReg
JOIN AssMas    ON AMId = PSRAMId
JOIN CorpClMas ON ACID = CCMID
WHERE  PProMon = '10'
    AND PProYe = '2014'
    AND PAPPSTA IS NULL
GROUP BY CCM_Name, CCM_ID

The SUM() in line 4 is the problem. And I don't see why you would put SUM() there at all, the 2 subqueries you're substracting will only ever give 1 result each since there's no GROUP BY. So value - value doesn't need a SUM() around it:

SELECT
    CCMID[Client],
    CCMName[Company],
    (
        SELECT (    
                Select SUM(PAm) from PaySalReg(NOLOCK) 
                Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
                where PSRAMId = AMId
                    and PEMDedTy = 'SSC' 
                    and PProYe = (SELECT DOYe FROM DefOMonth)
                    and PProMo = (SELECT DOMo-1 FROM DefOMonth)
            ) - (
                Select SUM(PAm) from PaySalReg(NOLOCK) 
                Join PayEleMas (NOLOCK) ON  PSPCo =PEPCo
                where PSRAMId = AMId
                    and PEMDedTy = 'PRTY'
                    and PProYe = (SELECT DOYe FROM DefOMonth)
                    and PProMo = (SELECT DOMo-1 FROM DefOMonth)
            )
        from AssMas(NOLOCK)
        WHERE ACID = CCMID
    ) AS [Net Pay],

    COUNT(PAMId) [No. of Associates] 

FROM PaySalReg
JOIN AssMas    ON AMId = PSRAMId
JOIN CorpClMas ON ACID = CCMID
WHERE  PProMon = '10'
    AND PProYe = '2014'
    AND PAPPSTA IS NULL
GROUP BY CCM_Name, CCM_ID

Sidenote: This query definitely looks like it could be refactored, but that was not your question :)

Upvotes: 0

Hasan Shouman
Hasan Shouman

Reputation: 2372

Try to move the aggregate queries to a new function(Scalar function) and call the function from your procedure .(replace the aggregate query by the function)

Upvotes: 0

Related Questions