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



Microsoft SQL Server Training Online Learning Classes Built in functions Aggregate 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

No comments:

Post a Comment