How To Use A Case Statement Within A Select Count Statement?

April 16, 2024 Post a Comment

I need to make a case statement. Depending on what the variables value is, it needs to select the correct column from the table StartDate and EndDate are different variables. There

Solution 1:

Assuming you want to count the number of records for which Column1 is in between the start and end date, then the following should work:

SELECTSUM(CASE WHEN COLUMN1 BETWEEN @StartDate AND @EndDate
           THEN 1 ELSE 0 END) AStallyFROMbankholidays

This approach is called conditional aggregation, and often you will also use a GROUP BY clause.

Update:

To handle your @Region variable, a query might look like this:

SELECTCASEWHEN@Region = 'val1' THEN COLUMN1
            WHEN@Region = 'val2' THEN COLUMN2
            WHEN@Region = 'val3' THEN COLUMN3
       END AS new_column   
FROM bankholidays

Note that I did not attempt to combine this with the first query because it would mix aggregate functions with non aggregate terms, and it might not make sense.

Solution 2:

SELECTCASEWHEN@Region = 'EW' THEN columnName
WHEN@Region = 'SC' THEN columnName
WHEN@Region = 'NI' THEN columnName
END AS newColumnName FROM bankholidaysT

Try something like this

Solution 3:

1) If you're not concerned about excluding nulls from your count, you don't need to specify a column name in your COUNT statement. i.e.

select count(Column1)

Will give the same result as

select count(1)

select count(*)

So long as column1 has no null values. If column1 does contain nulls, those aren't counted (so if there are 10 records, 3 of which have null values in column1, you'd get a result of 7 using count(column1) or a count of 10 using the other methods.

I mention this first as if you care about nulls then changing which column is used here makes sense; if you don't, go with the simpler logic of just count(1) / counnt(*).

All that said, here's how to change that column:

selectcount(
    case @Region 
        when 1 then Column1 
        when 2 then Column2 
        else Column3 
    end
)

2) If you want to change the column used in your WHERE statement, there are a couple of approaches:

SELECTCOUNT(1)
FROMbankholidaysWHEREcase @Regionwhen1 then Column1 
    when2 then Column2 
    else Column3        
end BETWEEN @StartDateAND@EndDate

SELECTCOUNT(1)
FROMbankholidaysWHERE (@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate)
or (@Region = 2 and Column2 BETWEEN @StartDate AND @EndDate
or (@Regionnot in (1,2) and Column3 BETWEEN @StartDateAND@EndDate

Personally I prefer the first style above, since it involves less repetition; however the second style offers the option to use different start & end dates for the different columns, or to add in other logic too, so is still worth being aware of.

Solution 4:

If you want to dynamically count records where a given column value is between two dates, use a dynamic query:

DECLARE@column NVARCHAR(MAX)
DECLARE@sql NVARCHAR(MAX)

SET@column='COLUMN1'--Set your desired column hereSET@sql='SELECT COUNT(*) FROM bankholidays WHERE '+@column+' BETWEEN '''+CONVERT(NVARCHAR,@StartDate,121) +''' AND '''+CONVERT(NVARCHAR,@EndDate,121) +''''EXEC@sql

Solution 5:

TRY THIS IF you want to count and check the column dynamically

DECLARE@sqlVARCHAR(500), 
    @RegionVARCHAR(100) ='COLUMN2', --It can be Column1, Column2.....@StartDate DATETIME ='2016-04-10',
    @EndDate DATETIME ='2016-04-15'SET@sql='
    SELECT
    COUNT('+@Region+')
    FROM  bankholidays
    WHERE '+@Region+' BETWEEN '''+CAST(@StartDateASVARCHAR) +''' AND '''+CAST(@EndDateASVARCHAR) +''''EXEC (@SQL)

--Query executed as below

SELECTCOUNT(COLUMN2)--It can be Column1, Column2.....FROM  bankholidays
WHERE COLUMN2 BETWEEN'Apr 10 2016 12:00AM'AND'Apr 15 2016 12:00AM'

Lab Sql