Functions

Function Category

Scalar Operate on a single row, return a single value.

Logical Compare multiple values to determine a single output.

Ranking Operate on a partition (set) of rows.

Rowset Return a virtual table that can be used in a FROM clause in a T-SQL statement.

Aggregate Take one or more input values, return a single summarizing value.

Scalar Function examples

SELECT [PickUpDateTime],

YEAR([PickUpDateTime]) AS PickUpYear,

DATENAME(mm, [PickUpDateTime]) AS PickUpMonth,

DAY([PickUpDateTime]) AS PickUpDay,

DATENAME(dw, [PickUpDateTime]) AS PickUpWeekDay,

DATEDIFF(yy,[PickUpDateTime], GETDATE()) AS YearsSincePickUp

FROM [dbo].[CarHires]

PickUpDateTime 2022-09-06 10:00

PickUpYear 2022

PickUpMonth September

PickUpDay 6

PickUpWeekDay Tuesday

YearsSincePickUp 1

SELECT BookingCost,

ROUND(BookingCost, 0) AS Rounded,

FLOOR(BookingCost) AS Floor,

CEILING(BookingCost) AS Ceiling,

SQUARE(BookingCost) AS Squared,

SQRT(BookingCost) AS Root,

LOG(BookingCost) AS Log,

BookingCost * RAND() AS Randomized

FROM [dbo].[CarHires];

SELECT CompanyName,

UPPER(CompanyName) AS UpperCase,

LOWER(CompanyName) AS LowerCase,

LEN(CompanyName) AS Length,

REVERSE(CompanyName) AS Reversed,

CHARINDEX(' ', CompanyName) AS FirstSpace,

LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,

SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName

FROM Sales.Customer;

BookingCost 25.5

Rounded 26

Floor 25

Ceiling 26

Squared 650.25

Root 5.04975247

Log 3.23867845

Randomized 1.00289761

Name My Company Name

UpperCase MY COMPANY NAME

LowerCase my company name

Length 15

Reversed emaN ynapmoC yM

FirstSpace 3

FirstWord My

RestOfName Company Name

Logical Function examples

SELECT AddressType, IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor

FROM Sales.CustomerAddress;

SELECT SalesOrderID, Status, CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus

FROM Sales.SalesOrderHeader; --> First value is 1

Ranking Functions

Original data template

Id Category Name Cost

1 Field Red Jacket 15.50

2 Trucker Red Jacket 15.50

3 Trucker Red Jacket 15.50

4 Field Yellow Jacket 16.00

5 Field Yellow Jacket 16.00

6 Trucker Yellow Jacket 16.50

7 Moto Blue Jacket 18.00

8 Moto Blue Jacket 20.00

9 Field Pink Jacket 21.00

SELECT TOP 100 [Id] ,[Name],[Cost],

RANK() OVER(ORDER BY Cost ASC) AS RankByPrice

FROM [dbo].[Products] AS p

ORDER BY RankByPrice;

Id Name Cost RankByPrice

1 Red Jacket 15.50 1

2 Red Jacket 15.50 1

3 Red Jacket 15.50 1

4 Yellow Jacket 16.00 4

5 Yellow Jacket 16.00 4

6 Yellow Jacket 16.50 6

7 Blue Jacket 18.00 7

8 Blue Jacket 20.00 8

9 Pink Jacket 21.00 9

SELECT Category, Name, Cost,

RANK() OVER(PARTITION BY Name ORDER BY Cost DESC) AS RankByPrice

FROM [dbo].[Products]

ORDER BY Category, RankByPrice;

Category Name Cost RankByPrice

Field Pink Jacket 21.00 1

Field Red Jacket 15.50 1

Field Yellow Jacket 16.00 2

Moto Blue Jacket 20.00 1

Moto Blue Jacket 18.00 2

Trucker Red Jacket 15.50 1

Trucker Yellow Jacket 16.50 1

Aggregate Functions

SELECT MIN([Cost]) AS Min1,

MAX([Cost]) AS Max1,

AVG(Cost) Avg1,

SUM(Cost) Sum1,

COUNT(Cost) Count1

FROM [dbo].[Products];

Min1 15.5

Max1 21

Avg1 17.111111

Sum1 154

Count1 9

There are 50 rows data. 1 row' branchId is null. COUNT ignores NULL

SELECT DISTINCT BranchId FROM [dbo].[Cars] --> Return 6 rows with NULL branch row

SELECT COUNT(BranchId) FROM [dbo].[Cars] --> Return as 49

SELECT COUNT(DISTINCT BranchId) FROM [dbo].[Cars] --> Return as 5