Skip to main content

15. Aggregate Functions

Image
By


SQL has many built-in functions for performing calculations on data.

Aggregate Functions


  1. 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);

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. Convert()
Change number stored as text to require numeric datatype.

  1. Getdate()
Return current date and time for server system clock.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. LEN()
Return number of characters.

  1. Replicate()
Return a character string repeated specified number of times.

  1. Patindex()
            It is same as charindex(). But we can use wildcards in this.






<<Prev



                                                       INDEX


Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                               ...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more