First see this table:
Here we have the Customer Name, Amount and Gender.
Now suppose somebody gives us 2 requirements for the table.
- Display the total number or count of "Male" and "Female" genders from the table by writing a single query.
- 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:
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:
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.
Conclusion: So in this article I have described how to fulfill some tricky SQL requirements in SQL Server.