Why Can't I Perform an Aggregate Function on an Expression Containing an Aggregate But I Can Do So by Creating a New Select Statement Around It

Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

SUM() in your example is a no-op - SUM() of a COUNT() means the same as just COUNT(). So neither of your example queries appear to do anything useful.

It seems to me that nesting aggregates would only make sense if you wanted to apply two different aggregations - meaning GROUP BY on different sets of columns. To specify two different aggregations you would need to use the GROUPING SETS feature or SUM() OVER feature. Maybe if you explain what you want to achieve someone could show you how.

Can't perform an aggregate function on an expression containing an aggregate or a subquery

You can't aggregate twice in the same SELECT statement and, even if you could, your min(len()) will return a single value: 2 since your minimum field length of #temp2 is 2. Counting that will just give you 1 because there is only 1 value to count.

You are wanting to get the count of how many fields have that minimum length so you'll need something like:

SELECT count(*)
FROM #temp2
WHERE len(min_col1_value) IN (SELECT min(len(min_col1_value)) FROM #temp1)

That WHERE clause says, only count values in #temp2 where the length is equal to the minimum length of all the values in #temp2. This should return 3 based on your sample data.

The same logic can be applied to either table for min or max.

SQL Server cannot perform an aggregate function on an expression containing an aggregate or a subquery, but Sybase can

One option is to put the subquery in a LEFT JOIN:

select sum ( t.graduates ) - t1.summedGraduates 
from table as t
    left join 
     ( 
        select sum ( graduates ) summedGraduates, id
        from table  
        where group_code not in ('total', 'others' )
        group by id 
    ) t1 on t.id = t1.id
where t.group_code = 'total'
group by t1.summedGraduates 

Perhaps a better option would be to use SUM with CASE:

select sum(case when group_code = 'total' then graduates end) -
    sum(case when group_code not in ('total','others') then graduates end)
from yourtable

SQL Fiddle Demo with both

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

You'll get much better performance generally if you try to avoid correlated subqueries anyway:

SELECT
    MT.column_1,
    MT.column_2,
    SUM(CASE WHEN P.product IS NULL THEN 1 ELSE 0 END) AS total
FROM
    My_Table MT
LEFT OUTER JOIN Products P ON P.product = MT.column_2
WHERE
    MT.is_rated = '1'
GROUP BY
    MT.column_1,
    MT.column_2

This assumes that there will only ever be at most one match in the Products table (Products, not Table_Products - of course it's a table so don't put that in the name). In other words, this will work if product is the PK (or an AK) of the Products table.

If that's not the case and you might have multiple matches in the Products table then you can JOIN to a subquery that uses DISTINCT on the product column.

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

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.

SQL-Case / Cannot perform an aggregate function on an expression containing an aggregate or a subquery

I'm not completely sure what you're trying to do, and I don't have your schema so I can't test this, but you might be able to move the SUMming out of the main query, here's an example using a CTE;

DECLARE @DATE AS SMALLDATETIME ,
    @BasTar DATE ,
    @Bittar DATE 

SET @DATE = GETDATE() - 1
SET @BasTar = GETDATE() - 1
SET @Bittar = GETDATE() - 1

; WITH CTE AS (
SELECT  ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END, 0) AS SATIST ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END, 0) AS SATISIV ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END, 0) AS IADET ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END, 0) AS IADEIV ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END, 0) AS SATISMIK ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END, 0) AS IADEMIK
FROM    TRANSACTION_HEADER TH WITH ( NOLOCK )
        INNER JOIN TRANSACTION_SALE TS WITH ( NOLOCK ) ON TS.FK_TRANSACTION_HEADER = TH.ID
        INNER JOIN STORE ST WITH ( NOLOCK ) ON ST.ID = TH.FK_STORE
                                               AND ST.NUM BETWEEN '3999' AND '9999'
WHERE   TH.STATUS = 0
        AND ST.DESCRIPTION NOT LIKE 'POL_%'
        AND ST.DESCRIPTION NOT LIKE 'SIS%'
        AND CONVERT(CHAR(11), TH.TRANS_DATE, 103) BETWEEN @BasTar
                                                  AND     @Bittar
)
SELECT
    SUM(SATIST) AS SATIST,
    SUM(SATISIV) AS SATISIV,
    SUM(IADET) AS IADET,
    SUM(IADEIV) AS IADEIV,
    SUM(SATISMIK) AS SATISMIK,
    SUM(IADEMIK) AS IADEMIK
FROM
    CTE

CASE WHEN THEN Cannot perform an aggregate function on an expression containing an aggregate or a subquery

You have a subquery inside sum(). That isn't allowed. This version uses a subquery to calculate the flag you want and then moves the filtering logic to the WHERE clause:

SELECT SUM(e.EmbVol)
FROM (SELECT e.*,
             (CASE WHEN EXISTS (SELECT 1 FROM dbo.CUMPLIDO c WHERE c.EmbCod = e.EmbCod AND c.CumpVol = 0)
                   THEN 1 ELSE 0
              END) as is_CumpVol_0
      FROM dbo.EMBARQUE e
      WHERE e.EmbEst NOT IN (0, 3, 6)
     ) e
WHERE e.EmbEst <> 7 OR is_CumpVol_0 = 0;

Subquery in an aggregate function

Use join instead :

SELECT t.id,MAX(t.date), COALESCE(s.max_date,DefaultDateHerE) as max_date_t2
FROM table1 t
LEFT JOIN(SELECT p.id,MAX(p.date) as max_date
          FROM Table2
          GROUP BY p.id) s
 ON(s.id = t.id)
GROUP BY t.id,COALESCE(s.max_date,DefaultDateHerE)

Why is this T-SQL query throwing an aggregate function error message?

May be this will help you

WITH CTE_TEST
AS
(   SELECT [PropertyCode], [BkgLeadTime], COUNT([Bkg Lead Time]) AS 'BLT_COUNT' 
    FROM BOOKINGLEADTIME_CTE
    GROUP BY [PropertyCode], [Bkg Lead Time]
)
SELECT SUM([BkgLeadTime]*[BLT_COUNT]) / SUM([BLT_COUNT]) FROM CTE_TEST

---

Related Topics