How to fix "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Question

I'm trying to count the number of rows in a table and output the result in a column. Counting the how many rows given in a specific data then display the result in a column. Can someone help me on this? Appreciate your help.

Below is my code:

SELECT DISTINCT BOM.STYLE_ID,
                BOM.SEASON_ID,
                BOM.Ad_compo_desc,
                BOM.CONTENT_CLASS,
                COUNT(CASE
                          WHEN BOM.CONTENT_CLASS = 'ART'
                               AND BOM.Ad_compo_desc LIKE '%emb%' THEN
                                 (SELECT COUNT(BOM.Ad_compo_desc))
                      END) AS EMBRO,
                COUNT(CASE
                          WHEN BOM.CONTENT_CLASS = 'ART'
                               AND BOM.Ad_compo_desc LIKE '%print%' THEN
                                 (SELECT COUNT(BOM.Ad_compo_desc))
                      END) AS PRINTING
FROM IPLEXSTY_AD_BOM_DTL BOM
WHERE CONTENT_CLASS = 'ART' --AND BOM.Ad_compo_desc IS NOT NULL
GROUP BY BOM.STYLE_ID,
         BOM.SEASON_ID,
         BOM.CONTENT_CLASS,
         BOM.Ad_compo_desc,
         BOM.CONTENT_CLASS

And it gives me an error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Answer 1

I believe you want something like this:

SELECT BOM.STYLE_ID, BOM.SEASON_ID,
       SUM(CASE WHEN BOM.Ad_compo_desc LIKE '%emb%' THEN 1 ELSE 0 END) AS EMBRO,
       SUM(CASE WHEN BOM.Ad_compo_desc LIKE '%print%' THEN 1 ELSE 0 END) AS PRINTING
FROM IPLEXSTY_AD_BOM_DTL BOM
WHERE CONTENT_CLASS = 'ART' --AND BOM.Ad_compo_desc IS NOT NULL
GROUP BY BOM.STYLE_ID, BOM.SEASON_ID;

Notice that unnecessary columns have been removed from both the SELECT and GROUP BY. Also, the WHERE clause filters out rows other than 'ART'; that filtering is not needed in the CASE.

Answer 2

Are you want to write something below

SELECT  BOM.STYLE_ID,
BOM.SEASON_ID,BOM.Ad_compo_desc,
BOM.CONTENT_CLASS,
sum(CASE WHEN BOM.CONTENT_CLASS = 'ART' AND BOM.Ad_compo_desc LIKE '%emb%' THEN 1 else 0 END)  AS EMBRO,
sum(CASE WHEN BOM.CONTENT_CLASS = 'ART' AND BOM.Ad_compo_desc LIKE '%print%' THEN 1 else 0 END)  AS PRINTING
FROM IPLEXSTY_AD_BOM_DTL BOM
WHERE CONTENT_CLASS = 'ART' --AND BOM.Ad_compo_desc IS NOT NULL
GROUP BY BOM.STYLE_ID,
BOM.SEASON_ID,BOM.CONTENT_CLASS,
BOM.Ad_compo_desc,BOM.CONTENT_CLASS

In your query, I found you used Distinct which is unnecessary because you use aggregate function. And what you tried in else clause that is not clear