17686

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)

Answer1:

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

Answer2:

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.

<strong>Update:</strong>

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.

Answer3:

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.

Answer4:

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

Answer5:

<strong>TRY THIS</strong> 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'

Answer6:

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

Recommend

  • Group By and add columns
  • how to force the use of cmov in gcc and VS
  • Conditional serialization with protobuf-net
  • How to get list of users who's birthday is today in MongoDB
  • Add custom field for WooCommerce CSV Export plugin - For customer first order [closed]
  • How to concat Pandas dataframe columns
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Approximate Order-Preserving Huffman Code
  • Not able to aggregate on nested fields in elasticsearch
  • Grails calculated field in SQL
  • Debug.DrawLine not showing in the GameView
  • Create DicomImage from scratch using Dcmtk
  • How to define custom class, title, and target in Link Browser for content elements and the new rte_c
  • Display images in Django
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • Moving mysql files across servers
  • $wpdb not working in file of WordPress plugin
  • Meteor: Do Something On Email Verification Confirmation
  • How to use an array of arrays with array_map(…) in PHP?
  • How to do unit test for HttpContext.Current.Server.MapPath
  • Paperclip, set path outside of rails root folder
  • R - Combining Columns to String Based on Logical Match
  • Get one-time binding to work for ng-if
  • Read text file and split every line in MSBuild
  • Is possible to count alias result on mysql
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Running a C# exe file
  • How to make Safari send if-modified-since header?
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • Do create extension work in single-user mode in postgres?
  • R: gsub and capture
  • How to pass list parameters for each object using Spring MVC?
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • embed rChart in Markdown
  • How does Linux kernel interrupt the application?
  • JaxB to read class hierarchy
  • How to load view controller without button in storyboard?