SQL Server Sum two count in case

Question

I'm trying to sum the two count case and ran into some errors says:

Column 'tmp.Col1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I've tried to add group by Col1, but it can't recognized Col1 as column.

SELECT 
   Col1,
   Col2,
   SUM(Col3 + Col4) AS 'In_Progress'
FROM
(
   SELECT
      COUNT(case when IO_Account_Handler_Contact IS NULL THEN 1 ELSE 0 END) AS 'Col1',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status IN ('OPEN-RELEASED','OPEN-CORRECTED') THEN 1 ELSE 0 END) AS 'Col2',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'For Issuance'  THEN 1 ELSE 0 END) AS 'Col3',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'OPEN-ACCEPTED' AND Completed_Quality_Review IS NOT NULL  THEN 1 ELSE 0 END) AS 'Col4'
   FROM Sales_Market
) tmp

Answer 1

You will need to add:

Group by Col1,Col2

to make it work. Not sure if that is what you want tough. when using any aggregation(sum,max,min etc) you will always need to group anything not aggregated. So in your case the two columns not just the 1. Or you could also sum(col1) and sum(col2).

So Either:

SELECT 
   sum(Col1),
   sum(Col2),
   SUM(Col3 + Col4) AS 'In_Progress'
FROM
(
   SELECT
      COUNT(case when IO_Account_Handler_Contact IS NULL THEN 1 ELSE 0 END) AS 'Col1',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status IN ('OPEN-RELEASED','OPEN-CORRECTED') THEN 1 ELSE 0 END) AS 'Col2',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'For Issuance'  THEN 1 ELSE 0 END) AS 'Col3',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'OPEN-ACCEPTED' AND Completed_Quality_Review IS NOT NULL  THEN 1 ELSE 0 END) AS 'Col4'
   FROM Sales_Market
) tmp

or

SELECT 
   Col1,
   Col2,
   SUM(Col3 + Col4) AS 'In_Progress'
FROM
(
   SELECT
      COUNT(case when IO_Account_Handler_Contact IS NULL THEN 1 ELSE 0 END) AS 'Col1',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status IN ('OPEN-RELEASED','OPEN-CORRECTED') THEN 1 ELSE 0 END) AS 'Col2',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'For Issuance'  THEN 1 ELSE 0 END) AS 'Col3',
      COUNT(case when Issuing_Country_Name ='U.S.A' AND Internal_status = 'OPEN-ACCEPTED' AND Completed_Quality_Review IS NOT NULL  THEN 1 ELSE 0 END) AS 'Col4'
   FROM Sales_Market
) tmp
group by Col1,Col2