Case Statement in SQL Server

First see this table:

casestmntsqlserver1.jpg
 
Here we have the Customer Name, Amount and Gender.

Now suppose somebody gives us 2 requirements for the table.
  1. Display the total number or count of "Male" and "Female" genders from the table by writing a single query.
  2. Update the Amount to Rs 8000 for the CustomerName "Shirsendu" and the remaining customers update the Amount to 1000 Rs.
Now for doing the first requirements we have to use a Count Query.

So normally what we do, we write the following query:

select COUNT(gender)as MALE ,COUNT (gender) as FEMALE from dbo.Customer where Gender='Male'and Gender='Female'

When you run the query it will look like the following figure:

casestmntsqlserver2.jpg
 
See here we are getting o,o count for Male and Female.

Because we can't execute a count of more than one column by simply giving the column name.

So here we have to to use a "Case" statement. Using "case" we can easily make the decesiion "If this that then this will be that".

Now the query is

SELECT COUNT(
         CASE
         WHEN Gender ='Male' THEN 'M'
         end
         )  as CountGenderMale ,
         COUNT(
         CASE
           WHEN Gender ='Female' THEN 'F'
         end
         )  as CountGenderFemale    
FROM dbo.Customer 

See here first we count the gender who is "male" and after that count the gender who is "female".

See after writing a "Case" Statement there must be "When" and "Then".

Otherwise you will get a syntex error.

Now see the following figure: 

casestmntsqlserver3.jpg 

See here we are getting the total count of "Male=6" and "Female=5".

Now comes to the second requirement.

Update the Amount to Rs 8000 for the customer "Shirsendu" and the remaining customers update the amount to 1000 Rs.

So here we will also use a "Case" statement in the update query.

update Customer
set Amount=case when CustomerName ='shirsendu' then 8000
else
1000
End

See here we update the amount to 8000 where CustomerName ='shirsendu' and rest of the part 1000 by giving "Else" statement.

casestmntsqlserver4.jpg
 
Conclusion: So in this article I have described how to fulfill some tricky SQL requirements in SQL Server. 

Up Next
    Ebook Download
    View all
    Learn
    View all