Get aggregate function message when I add a EXIST clause-CodePudding

I have the following query which works fine:

Select 
SUM(CASE
    WHEN MethodId NOT IN (1,2) THEN 1
    ELSE 0
   END) Total
 FROM pll_data

Next, I need to check if data exist in another table for the same MethodId. I get the following error (Cannot perform an aggregate function on an expression containing an aggregate or a subquery. ) when I add the EXISTS clause that preferences another table:

  SELECT 
  SUM(CASE
   WHEN 
    EXISTS(Select 1 from pll_dataSys pp WHERE pp.PNum = p.PNum AND pp.MethodID IN (1,2,3)) OR  -- I added this line of code
     MethodId NOT IN (1,2) THEN 1
   ELSE 0
  END) Total
FROM pll_data p

NOTE: I get the following error when I add the EXISTS clause:

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

CodePudding user response：

Maybe like this

SELECT 
  SUM(CASE
   WHEN ISNULL(result,0)=1 OR
      MethodId NOT IN (1,2) THEN 1
   ELSE 0
  END) Total
FROM pll_data p 
 OUTER APPLY 
 ( Select 1 as result 
     from pll_dataSys pp 
      WHERE pp.PNum = p.PNum AND pp.MethodID 
   IN (1,2,3)) A

CodePudding user response：

You can left join with pp with the criteria that you specified and check it against null in your case-when. To protect against duplicates, you can ensure that pp is unique by excluding all other pp lines for each result record:

  SELECT 
  SUM(CASE
   WHEN 
    NOT (pp.Num IS NULL) THEN 1
    ELSE 0
   ELSE 0
  END) Total
FROM pll_data p
LEFT JOIN pll_dataSys pp
ON (p.PNum = pp.PNum AND pp.MethodID IN (1,2)) OR pp.MethodId NOT IN (1, 2)
LEFT JOIN pll_dataSys pp_not_existent
ON ((p.PNum = pp_not_existent.PNum AND pp_not_existent.MethodID IN (1,2)) OR pp_not_existent.MethodId NOT IN (1, 2)) AND pp.pp_id < pp_not_existent.pp_id
WHERE pp_not_existent.pp_IS NULL