SQL has many built-in
functions for performing calculations on data.
Aggregate Functions
- AVG() Function
The AVG() function returns the average value of
a numeric column.
§ Syntax
SELECTAVG(column_name)FROMtable_name
§ Example
SQL statement gets the average
value of the "Price" column from the "Products" table:
SELECTAVG(Price)ASPriceAverageFROMProducts
SQL statement selects the
"ProductName" and "Price" records that have an above
average price:
SELECTProductName, Price FROM Products
WHERE Price>(SELECTAVG(Price)FROM Products);
- COUNT() Function
The COUNT() function returns the number of rows
that matches a specified criteria.The COUNT(column_name) function returns the
number of values (NULL values will not be counted) of the specified column:
§ Syntax
SELECTCOUNT(column_name)FROMtable_name;
- MAX() Function
The MAX() function returns the largest value of
the selected column.
§ Syntax
SELECTMAX(column_name)FROMtable_name;
§ Example
SQL statement gets the largest value of the
"Price" column from the "Products" table:
SELECTMAX(Price)ASHighestPriceFROM Products;
- MIN() Function
The MIN() function returns the smallest value of
the selected column.
§ Syntax
SELECTMIN(column_name)FROMtable_name;
§ Example
SQL statement gets the smallest value of the
"Price" column from the "Products" table:
SELECTMIN(Price)ASSmallestOrderPriceFROM Products;
- SUM() Function
The SUM() function returns the total sum of a
numeric column.
§ Syntax
SELECTSUM(column_name)FROMtable_name;
§ Example
SQL statement finds the sum of all the
"Quantity" fields for the "OrderDetails" table:
SELECTSUM(Quantity)ASTotalItemsOrderedFROMOrderDetails;
Scalar Functions
1.
UPPER() Function
The UPPER() function converts the value of a field to uppercase.
§ Syntax
SELECT UPPER(column_name)FROMtable_name;
§ Example
SELECT UPPER(CustomerName)AS Customer, City
FROM Customers;
- LOWER() Function
The LOWER() function converts the value of a
field to lowercase.
§ Syntax
SELECT LOWER(column_name)FROMtable_name;
§ Example
SELECT LOWER(CustomerName)AS Customer, City
FROM Customers;
- MID() Function
The MID() function is used to extract characters
from a text field.
§ Syntax
SELECT MID(column_name,start,length) AS some_name FROM table_name;
§ Example
SELECT MID(City,1,4)ASShortCity
FROM Customers;
- LEN() Function
The LEN() function returns the length of the
value in a text field.
§ Syntax
SELECTLEN(column_name)FROMtable_name;
§ Example
SELECTCustomerName,LEN(Address)asLengthOfAddress
FROM Customers;
- ROUND() Function
The ROUND() function is used to round a numeric
field to the number of decimals specified.
§ Syntax
SELECTROUND(column_name,decimals)FROMtable_name;
§ Example
SELECTProductName,ROUND(Price,0)ASRoundedPrice
FROM Products;
- Convert()
Change number stored as text to require numeric datatype.
- Getdate()
Return current date and time for server system
clock.
- DateDiff()
Compares and return different between date item,
such as days, weeks,mins,seconds,hours etc. When used with where clause return
record that meet a range of date.
- DatePart()
Returns a value equal to part of a date.
Part of date here is, take weeks from somewhere
else and days from else, merge them.
- SoundEx()
Convert string into 4 digit code. It is used to
find list of potential match.
§ Example
If we heard something which we are not sure
about, in such situation we can use SOUNDEX("xyz") It will return all
related to that spell.
- CharIndex():
Select CHARINDEX('t',StrName) AS Name, StrName
from Datatext
Name StrName
0 Urvashi
1 Tulsi
4 Sriti
§ Used to search
for the match of character in a column.
- LEN()
Return number of characters.
- Replicate()
Return a character string repeated specified
number of times.
- Patindex()
It is same as charindex(). But we can use wildcards in
this.
INDEX
Comments
Post a Comment