SQL Server "Cannot Perform an Aggregate Function on an Expression Containing an Aggregate or a Subquery", But Sybase Can

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.

SQL Error Cannot perform an aggregate function on an expression containing an aggregate or a sub query.

According to desired reasult comment, try this

SELECT [id]
  ,[name]
  , r = max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
    + max(CASE WHEN [code] IN (8) then 80 else 0 end)
  FROM 
    -- your table here
    (values (1, 'ali',4)
            ,(1, 'ali',1)
            ,(1, 'ali',8)
    ) as t(id, name,code)
  GROUP BY id, name;

EDIT another story for excluding something.
Any of 1,2,4 give 100 plus if it was only 4 without (1,2) add 400.

SELECT [id]
  ,[name]
  , r = max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
    + min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
        * max(CASE WHEN [code]  IN (4) then 400 else 0 end)
    + max(CASE WHEN [code] IN (8) then 80 else 0 end)
  FROM 
    -- your table here
    (values (1, 'ali',4)
            ,(1, 'ali',1)
            ,(1, 'ali',8)
            ,(2, 'ali',4)
            ,(2, 'ali',8)
    ) as t(id, name,code)
  GROUP BY id, name;

EDIT 2 If you need multiply scores, replace + with * and convert 0 into 1.

SELECT [id]
  ,[name]
  ,r = isnull(nullif(
        max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
      ,0),1)
    * isnull(nullif(
       min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
        * max(CASE WHEN [code]  IN (4) then 400 else 0 end)
      ,0),1)
    * isnull(nullif(
        max(CASE WHEN [code] IN (8) then 80 else 0 end)
      ,0),1)
  FROM 
    -- your table here
    (values (1, 'ali',4)
            ,(1, 'ali',1)
            ,(1, 'ali',8)
            ,(2, 'ali',4)
            ,(2, 'ali',8)
    ) as t(id, name,code)
  GROUP BY id, name;

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

I feel like your query is written in a very strange way that will make it difficult to debug. A few thoughts:

• Stop Converting everything you are selecting (at least until you get
  the results you want). This will make your code more concise and
  easier to debug

• If possible, stop with the subqueries in your select statement. For example:

  ISNULL((SELECT SA.SD_DATA
  FROM SDDATA AS SA
  WHERE SA.DEBT_KEY=D.DEBT_KEY
  AND SA.SD_KEY=2),' ') AssignmentType,

can be converted to

ISNULL(SA.SD_DATA,' ')
...
...
FROM DEBT AS D
LEFT JOIN SDDATA AS SA 
ON sa.DEBT_KEY = d.DEBT_KEY

• Stop using implied joins

• Read up on how GROUP BY works with aggregate functions.

This isn't a solution for your exact problem, but you need to get this query into a workable state and do some basic research if you want to resolve this issue.

---

Related Topics