How to use a CASE statement within a SELECT COUNT statement?

Question

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 is a variable i created called Region which should determine what column the query selects.

EDIT: Region can either be 'EW' for England and Wales, 'SC' for Scotland or 'NI' for Northern Ireland. If it is EW it should select column 1, SC for column 2, NI for column 3

SELECT
COUNT(COLUMN1)
FROM  bankholidays
WHERE COLUMN1 BETWEEN @StartDate AND @EndDate)

Answer 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:

SELECT SUM(CASE WHEN COLUMN1 BETWEEN @StartDate AND @EndDate
           THEN 1 ELSE 0 END) AS tally
FROM bankholidays

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:

SELECT CASE WHEN @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.

Answer 2

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)

Or

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:

select count(
    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:

SELECT COUNT(1)
FROM  bankholidays
WHERE case @Region 
    when 1 then Column1 
    when 2 then Column2 
    else Column3        
end BETWEEN @StartDate AND @EndDate

or

SELECT COUNT(1)
FROM  bankholidays
WHERE (@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate)
or (@Region = 2 and Column2 BETWEEN @StartDate AND @EndDate
or (@Region not in (1,2) and Column3 BETWEEN @StartDate AND @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.

Answer 3

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

Try something like this

Answer 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 here
SET @sql = 'SELECT COUNT(*) FROM bankholidays WHERE ' + @column + ' BETWEEN ''' + CONVERT(NVARCHAR,@StartDate,121) + ''' AND ''' + CONVERT(NVARCHAR,@EndDate,121) + ''''

EXEC @sql

Answer 5

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

DECLARE @sql VARCHAR(500), 
    @Region VARCHAR(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(@StartDate AS VARCHAR) + ''' AND ''' + CAST(@EndDate AS VARCHAR) + ''''

EXEC (@SQL)

--Query executed as below

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

Answer 6

declare  @Region  varchar(10)
set @Region='EW'

SELECT 
COUNT(*) OVER(PARTITION BY 1) as TotalRows,
Case  
    when @Region ='EW' THEN   Column1 
    when @Region ='SC' THEN   Column2
    when @Region ='NI' THEN   Column3
end as [NewColumn]
FROM [BankHolidays] 
WHERE COLUMN1 BETWEEN @StartDate AND @EndDate