SqlTutorial-1 : Lesson-11 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Aggregate and Scalar

The following are various types of functions provided SQL-server as part of T-SQL language library.

BUILT-IN FUNCTIONS

1. Aggregate functions
2. Scalar functions / global variables @@variable
3. String functions
4. Data Conversion functions
5. Null checking functions.
6. Date functions
7. Mathematical functions
8. Analytical functions

1. Aggregate functions ( 6 methods)

• Aggregate functions are applied to a group of data values from a column. But not on single value nor on a group of values of a row.
• Aggregate functions always return a single value. The T-SQL language support following functions.
1. AVG()   : Returns average of given column; The column must contain numeric values.
2. MAX() : Returns Max-value from given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
3. MIN()  :  Returns Mininimum value from the given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
4. SUM()  :  Returns Sum of values avialable in given column. The column must contain numeric values.
5. COUNT()  : Returns No. of rows or values in the given column; The only aggregate function not being applied to column is COUNT(*).
6. COUNT_BIG() :  Similar to COUNT() except returns BIGINT data instead of int.

1.1 - AVG():

Calculates the average for selected records of numeric data in a column or the average for DISTINCT (unique) values of the selected records.

Syntax

AVG ( [ ALL | DISTINCT ] Expression ); 

Eamples

1. Select AVG (qty) 
   From Sales;
2. Select AVG(DISTINCT Salary)
   FROM Employee;
3. Select AVG(SalesPrice), AVG(AverageCost)
   From CD WHERE Classif_ID = 1
4. Select Titles.title, Titles.price
   From Titles JOIN Sales ON Sales.title_id = Titles.title_id
   WHERE Sales.qty > (SELECT AVG(qty) FROM sales)

1.2 MAX():

Returns the largest value in a column of all selected records by the query of any char, number, or datetime datatype.

Syntax :

MAX ( [ ALL | DISTINCT ] expression )

Examples:

1. Find Last ordered date for Sales

   Select MAX(ord_date)
   FROM  Sales;

2. The use of the aggregate function MAX in subquery

   SELECT *
   FROM employee
   WHERE start_date = (SELECT MAX(start_date) FROM employee WHERE id > 3)

1.3: MIN():

Returns the smallest value in a column of all selected records by the column of any char, number, or datetime datatype.

Syntax :

MIN ( [ ALL | DISTINCT ] expression )

Examples:

1. Find first order_date

   Select MIN(ord_date)
   From sales

2. Finding the Earliest Date Using MIN()

   SELECT *
   FROM Employee
   WHERE Start_Date =( SELECT MIN (Start_Date) FROM Employee)

1.4 SUM :

Returns the total value in a column of all selected records or all the DISTINCT (unique) values selected by the query.

Syntax :

SUM ( [ ALL | DISTINCT ] expression )

• This function is used for only number datatypes.
• All NULL values are ignored.

Examples:

1. SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY dept_name, emp_cnt;

2. Sum with CUBE

   SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY dept_name, emp_cnt WITH CUBE;

3. SUM with WITH ROLLUP

   SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY emp_cnt, dept_name WITH ROLLUP

1.5 COUNT():

Returns the total count of records in a column or the count for DISTINCT(unique) values selected in column.

Syntax :

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 

Examples:

1. All NULL values are ignored in the total count.

   (a) select COUNT(qty) from sales;
   (b) select COUNT(DISTINCT Classif_ID) from CD

2. COUNT(*) returns the total count of all records (rows) in the column, including NULL values of selected records by the query. DISTINCT is not allowed with this function.

   Select COUNT(*) 
   From Sales;

3. A common mistake with DISTINCT is to code the DISTINCT before the COUNT statement.

   SELECT DISTINCT COUNT(Classif_ID)   --DISTINCT no effect here
   From CD 

4. Using ISNULL to count all values including NULLs.

   SELECT COUNT(ISNULL(name, ' ')) AS Expr1
   FROM student

5. Count table row in dynamic sql

   EXEC(N ' SELECT COUNT(*)
                   FROM ' + @objectname + N';');

Difference between COUNT(*), COUNT(col) & COUNT(DISTINCT col)

• COUNT(*) - Returns the number of records in the group(table). This includes NULL values and duplicates.
• COUNT(ALL expression)- Returns no. of values in the given column including duplicates & excluding null values.
• COUNT(DISTINCT expression) similar to COUNT(ALL column) except excludes duplicates.

2. Scalar functions - Global Variables (@@)

• These are special types of functions that doe not takes any parameters and returns single value
• These are invocable as @@function name; 
• These are also called Global Variables.

@@VERSION, @@LANGUAGE, @@LangID, @@servername, @@Max_connection, @@Connections, @@CPU_BUSY, @@Idle, @@Total_Errors, @@NestLevel, @@TextSize, @@Max_precision, @@DateFirst, @@Cursor_Rows, @@Fetch_Status, @@Error, @@RowCount, @@Trancount, @@Identit;

Function Name	Description
1 )   @@VERSION	Returns version, processor architecture, build date, and operating system for the current installation of SQL Server. Example: SELECT @@Version è    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)         Feb  9 2007 22:47:07        Copyright (c) 1988-2005 Microsoft Corporation        Express Edition on Windows NT 5.2 (Service Pack 2, v.4354)
2 )  @@LANGUAGUE	Returns the name of the language currently being used. Example (1)  Select @@Languageè us_english  //Default (2)  SET LANGUAGE Italian // Customizing        Select @@LanguageèItalian
3 )  @@LangID	Returns the local language identifier (ID) of the language that is currently being used. Example: Select @@LangID è 6
4 )  @@servername	Returns the name of the local server that is running SQL Server. Example: Select @@Servernameè abc\SQLEXPRESS
5 )  @@Max_connection	Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. Example: Select @@Max_Connection è 32767
6 )  @@Connections	Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started. Example:  SELECT @@CONNECTIONS è208
7 )  @@CPU_BUSY	Returns the time that SQL Server has spent working since it was last started Example: Select CPU_BUSYè 97 mnts.
8 )  @@Idle	Returns the time that SQL Server has been idle since it was last started. Example: Select @@Idle è 373820 mnts
9 )  @@Total_Errors	Returns the number of disk write errors encountered by SQL Server since SQL Server last started Example: Select @@Total_Errors.
10 )   @@NestLeve Note: Use it with recursion of procedure l	Returns the nesting level of the current stored procedure execution (initially 0) on the local server CREATE PROCEDURE SP_OuterProc AS     SELECT @@NESTLEVEL AS 'Outer Level';     EXEC SP_InnerProc; GO CREATE PROCEDURE SP_InnerProc AS     SELECT @@NESTLEVEL AS 'Inner Level'; GO EXECUTE SP_OuterProc; GO Outer Level      Inner Level -----------             ----------- 1                        2
11 )  @@TextSize	Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns. Examples:   (1) Select @@TextSize è 2147483647 //default   (2) SET TEXTSIZE 2048     //customizing         SELECT @@TEXTSIZE è 2048
12 )   @@Max_precision	Returns the precision level used by decimal and numeric data types as currently set in the server. Example: Select @@Max_precision è 38
13 )  @@DateFirst	Returns the current value, for the session, of SET DATEFIRST. Examples: (1)    Select @@DateFirst è7 //default (2)    SET DATEFIRST 5  //Customizing          Select @@DateFirst è 5