Cannot perform an aggregate function on an expression containing an aggregate or a subquery sql server 2012

0
votes

I am performing a query and its showing an error Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

the query is 

 SELECT
 tbl_Product.ID,
 tbl_Product.ArticleID,
 tbl_Product.Title, 
 tbl_Product.Description,
 tbl_Product.Price,
 tbl_ProductType.Name,
 tbl_Status.StatusName,     
 tbl_VisibilityStatus.VisibilityStatus,
 MAX(
     CASE 
         WHEN tbl_RelatedProduct.TypeOfRelation = 1 THEN 'Bundle' 
         WHEN tbl_Product.ID IN 
        ( 
         select tbl_RelatedProduct.Product2ID 
         from tbl_RelatedProduct 
         where tbl_RelatedProduct.Product1ID=9 and tbl_RelatedProduct.TypeOfRelation=1)            THEN 'Bundle'
        END
        ) 'Bundle',
 MAX(
     CASE 
         WHEN tbl_RelatedProduct.TypeOfRelation = 2 THEN 'Follower' END) 'Follower',
 MAX(
     CASE 
         WHEN tbl_RelatedProduct.TypeOfRelation = 3 THEN 'Related' END) 'Related'
 FROM            
 tbl_Product inner JOIN
 tbl_ProductType ON tbl_Product.ProductTypeId = tbl_ProductType.ID Inner JOIN
 tbl_Status ON tbl_Product.StatusID = tbl_Status.ID Inner JOIN
 tbl_VisibilityStatus ON tbl_Product.VisibilityID = tbl_VisibilityStatus.ID
 left JOIN tbl_RelatedProduct ON tbl_Product.ID = tbl_RelatedProduct.Product1ID
 group by      
 tbl_Product.ID,
 tbl_Product.ArticleID,
 tbl_Product.Title,
 tbl_Product.Description,
 tbl_Product.Price,
 tbl_ProductType.Name,
 tbl_Status.StatusName, 
 tbl_VisibilityStatus.VisibilityStatus
 order by tbl_Product.Title

ANyone know how to help on this...plsss

3
sqlsql-serversql-server-2012
Keep in mind that the CASE expression evaluates the conditions from top to bottom and stops when a match is found. So the second WHEN in your 1st CASE will only be hit when tbl_RelatedProduct.TypeOfRelation <> 1, is that correct? I now see there's the same condition in the subquery, so that subquery will never produce any rows IMO. - NickyvV
Furthermore, that CASE always outputs 'Bundle' (or NULL if no match is found), so why use the MAX? - NickyvV

3 Answers

0
votes

The below line is causing an issue:

WHEN tbl_Product.ID IN 
    ( 
     select tbl_RelatedProduct.Product2ID 
     from tbl_RelatedProduct 
     where tbl_RelatedProduct.Product1ID=9 and tbl_RelatedProduct.TypeOfRelation=1)            THEN 'Bundle'
    END
    ) 'Bundle',

You are using MAX with CASE and one of the statements within CASE uses a subquery, which is causing the error. You might want to consider using joins instead of subqueries to implement this.

0
votes

SELECT tbl_Product.ID,tbl_Product.ArticleID,tbl_Product.Title,tbl_Product.Description,tbl_Product.Price,tbl_ProductType.Name,tbl_Status.StatusName, tbl_VisibilityStatus.VisibilityStatus, Case when ((select count(1) from tbl_RelatedProduct where tbl_RelatedProduct.TypeOfRelation = 1 and tbl_RelatedProduct.Product1ID = tbl_Product.Id) > 0) then 1 when ((select count(1) from tbl_RelatedProduct where tbl_RelatedProduct.TypeOfRelation = 1 and tbl_RelatedProduct.Product2ID = tbl_Product.Id) > 0) then 1 else 0 end as Bundle, (select count(1) from tbl_RelatedProduct where tbl_RelatedProduct.TypeOfRelation = 2 and tbl_RelatedProduct.Product1ID = tbl_Product.Id) as Follower, (select count(1) from tbl_RelatedProduct where tbl_RelatedProduct.TypeOfRelation = 3 and tbl_RelatedProduct.Product1ID = tbl_Product.Id) as Related

FROM tbl_Product inner JOIN tbl_ProductType ON tbl_Product.ProductTypeId = tbl_ProductType.ID Inner JOIN tbl_Status ON tbl_Product.StatusID = tbl_Status.ID Inner JOIN tbl_VisibilityStatus ON tbl_Product.VisibilityID = tbl_VisibilityStatus.ID

Its resolved with this query :)

0
votes

I think I see what you are trying to do, and it is a bit strange. I would output this all as a single column, and lose the MAX aggregate as follows:

SELECT 
    p.ID,
    p.ArticleID,
    p.Title,
    p.Description,
    p.Price,
    pt.Name,
    s.StatusName,
    v.VisibilityStatus,
    CASE
        WHEN rp.TypeOfRelation = 1 OR (rp2.Product1ID = 9 AND rp2.TypeOfRelation = 1)
            THEN 'Bundle'
        WHEN rp.TypeOfRelation = 2
            THEN 'Follower'
        WHEN rp.TypeOfRelation = 3
            THEN 'Related'
        ELSE Null
    END AS Relation
FROM 
     tbl_Product p
     INNER JOIN tbl_ProductType pt
         ON p.ProductTypeId = pt.ID
     INNER JOIN tbl_Status s
         ON p.StatusID = s.ID
     INNER JOIN tbl_VisibilityStatus v
         ON p.VisibilityID = v.ID
     LEFT JOIN tbl_RelatedProduct rp
         ON p.ID = rp.Product1ID
     LEFT JOIN tbl_RelatedProduct rp2
         ON p.ID = rp2.Product2ID
ORDER BY p.Title

If you would still like to have them in separate columns, just break up the CASE statement as follows:

SELECT 
    p.ID,
    p.ArticleID,
    p.Title,
    p.Description,
    p.Price,
    pt.Name,
    s.StatusName,
    v.VisibilityStatus,
    CASE
        WHEN rp.TypeOfRelation = 1 OR (rp2.Product1ID = 9 AND rp2.TypeOfRelation = 1)
            THEN 'Bundle'
        ELSE Null
    END AS Bundle,
    CASE
        WHEN rp.TypeOfRelation = 2
            THEN 'Follower'
        ELSE Null
    END AS Follower,
    CASE
        WHEN rp.TypeOfRelation = 3
            THEN 'Related'
        ELSE Null
    END AS Related
FROM 
     tbl_Product p
     INNER JOIN tbl_ProductType pt
         ON p.ProductTypeId = pt.ID
     INNER JOIN tbl_Status s
         ON p.StatusID = s.ID
     INNER JOIN tbl_VisibilityStatus v
         ON p.VisibilityID = v.ID
     LEFT JOIN tbl_RelatedProduct rp
         ON p.ID = rp.Product1ID
     LEFT JOIN tbl_RelatedProduct rp2
         ON p.ID = rp2.Product2ID
ORDER BY p.Title