Page 29 - SQL

Alternative:

SELECT
COUNT(Id) as ItemsCount,
SUM (
CASE PriceRating
WHEN 'Expensive' THEN 1
ELSE 0
END
) AS ExpensiveItemsCount
FROM ItemSales

Shorthand CASE in SELECT

CASE's shorthand variant evaluates an expression (usually a column) against a series of values.
This variant is a bit shorter, and saves repeating the evaluated expression over and over again.
The ELSE clause can still be used, though:

SELECT Id, ItemId, Price,
CASE Price WHEN 5 THEN 'CHEAP'
WHEN 15 THEN 'AFFORDABLE'
ELSE 'EXPENSIVE'
END as PriceRating
FROM ItemSales

A word of caution. It's important to realize that when using the short variant the entire statement is
evaluated at each WHEN. Therefore the following statement:

SELECT
CASE ABS(CHECKSUM(NEWID())) % 4
WHEN 0 THEN 'Dr'
WHEN 1 THEN 'Master'
WHEN 2 THEN 'Mr'
WHEN 3 THEN 'Mrs'
END

may produce a NULL result. That is because at each WHEN NEWID() is being called again with a new
result. Equivalent to:

SELECT
CASE
WHEN ABS(CHECKSUM(NEWID())) % 4 = 0 THEN 'Dr'
WHEN ABS(CHECKSUM(NEWID())) % 4 = 1 THEN 'Master'
WHEN ABS(CHECKSUM(NEWID())) % 4 = 2 THEN 'Mr'
WHEN ABS(CHECKSUM(NEWID())) % 4 = 3 THEN 'Mrs'
END

Therefore it can miss all the WHEN cases and result as NULL.

CASE in a clause ORDER BY

We can use 1,2,3.. to determine the type of order:

https://riptutorial.com/ 11