3 - SQL Agrregate /String Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Function	Description
AVG()	Returns the average value
COUNT()	Returns the number of rows
FIRST()	Returns the first value
LAST()	Returns the last value
MAX()	Returns the largest value
MIN()	Returns the smallest value
ROUND()	Rounds a numeric field to the number of decimals specified
SUM()	Returns the sum

• SELECT AVG(Price) AS PriceAverage FROM Products; //returns a single value (the avg of all the prices column in products table)
• SELECT ProductName, Price FROM Products
  WHERE Price>(SELECT AVG(Price) FROM Products);

• SELECT COUNT(DISTINCT column_name) FROM table_name;
• SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;
• SELECT COUNT(*) AS NumberOfOrders FROM Orders;//determines numbers of rows in Orders table

• SELECT MAX(Price) AS HighestPrice FROM Products;
• SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.

• SELECT ROUND(column_name,decimals) FROM table_name;
• SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

• SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

_______________________________________________________________________

SQL String Functions

Function	Description
CHARINDEX	Searches an expression in a string expression and returns its starting position if found
CONCAT()
LEFT()
LEN() / LENGTH()	Returns the length of the value in a text field
LOWER() / LCASE()	Converts character data to lower case
LTRIM()
SUBSTRING() / MID()	Extract characters from a text field
PATINDEX()
REPLACE()
RIGHT()
RTRIM()
UPPER() / UCASE()	Converts character data to upper case