Use multiple criteria in SQL Server and return value

I have the following criteria that won’t run.

I have tried:

SELECT
    Main_ID AS [First_Custom_Column],
    CASE 
       WHEN Column_1 = 'Y' AND
            SUM(CASE WHEN Column_3 > 1 THEN Column_2 END) > 0 AND
            COUNT(CASE WHEN Column_3 > 1 THEN 2 END) > 1 # Will fail, Error below. 
           THEN 1 
           ELSE 0
    END AS [Second_Custom_Column]
FROM
    [Table_Name] 
WHERE 
    [Date] = '2020-11-01';

Error: ‘Main_ID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Then I tried this:

SELECT
    Main_ID AS [First_Custom_Column],
    CASE 
       WHEN Column_1 = 'Y' AND
            (CASE WHEN Column_3 > 1 THEN 1 END) > 0 AND
            (CASE WHEN Column_3 > 1 THEN 2 END) > 1
           THEN 1 
           ELSE 0
    END AS [Second_Custom_Column]
FROM 
    [Table_Name] 
WHERE 
    [Date] = '2020-11-01';

Error: The conversion of the varchar value ‘3344511715’ overflowed an int column.

I am essentially attempting to replicate COUNTIF and SUMIF from Excel. Once fully replicated I’ll turn the query into a function, but first I need it to work. The third column, Column_3, has duplicated data that the function is intended to count to see if they recur.

Has anyone successfully replicated COUNTIF with multiple criteria? How?

Answer

Got it to work:

COUNT([Column_3]) OVER (PARTITION BY [Column_3] ORDER BY [Column_3]) AS [xCountIF],
SUM([Column_2]) OVER (PARTITION BY [Column_3] ORDER BY [Column_3]) AS [xSumIF]

If anyone has a similar problem respond to this answer and I’ll walk you through it.

Advertisement

Answer