Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Built-in aggregate Functions

Built-in aggregate Functions:

Aggregate functions perform a calculation on a set of values and return a single value.Aggregate functions are deterministic,Aggregate functions ignore null values,Except Count() Function.


Aggregate functions can be used as expressions only in the following:

·         The select list of a SELECT statement (either a subquery or an outer query).

·         A HAVING clause

SQL Server Provides Following Aggregate functions:

1.       AVG()

2.       MIN()

3.       CHECKSUM_AGG()

4.       SUM()

5.       COUNT()

6.       STDEV()

7.       COUNT_BIG()

8.       STDEVP()

9.       GROUPING()

10.   VAR()

11.   GROUPING_ID()

12.   VARP()

13.   MAX()

SUM ():

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause

Syntax:

SUM ( [ ALL | DISTINCT ] expression )

 

Example:

-- Get total order amount using Sum() Aggrigate function  in shop by month wise and year wise

-- ROW_NUMBER() function returns unique row number value within a partition

-- by OrderYear and OrderMonth

SELECT ROW_NUMBER() OVER (ORDER BY OrderSummery.OrderYear, OrderSummery.OrderMonth) AS [No.]

    , OrderSummery.OrderYear, OrderSummery.OrderMonth

    , OrderSummery.OrderAmountByMonth

FROM

(SELECT YEAR(OrderDate) OrderYear

    , MONTH(OrderDate) OrderMonth

    , SUM(Total) OrderAmountByMonth

FROM [Order]

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

) OrderSummery


 

User Defined Scalar Function

User Defined Scalar Function:

    User-defined scalar functions return a single value, according to requirement any return type you can use in function.

 The timestamp data type, user-defined data type, and no scalar types, such as table or cursor, are not supported. The body of the function, defined in a BEGIN...END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date, ,>

-- Description:   User defined function returns full name

-- =============================================

ALTER FUNCTION GetUserFullName

(

      -- Add the parameters for the function here

 @UserFirstName varchar(70),

 @UserLastName varchar(70)

)

RETURNS NVARCHAR(250)

AS

BEGIN RETURN (SELECT @UserFirstName + ' '+ @UserLastName);

END

 

GO

 

Use of Function in select List:



 

Inline Table-Valued Function

Inline Table-Valued Function:

User Defined functions returns table variable as result of actions, logic performed by function.

Function allows only single Select statement enclosed by parentheses, there is no function Body delimited by BEGIN and END. The RETURNS clause contains only the keyword table.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date,,>

-- Description:   Return total amount spent by user in web shop till date

-- =============================================

CREATE FUNCTION GetTotalSpentByUser_FN

(    

      @LoginName Nvarchar(50)

)

RETURNS TABLE

AS

RETURN

(

-- Add the SELECT statement with parameter references here

SELECT U.LoginName,SUM(O.total) TotalSpent

FROM [order] O INNER JOIN [User] U on O.UserID=U.Id WHERE U.LoginName=@LoginName GROUP BY U.LoginName

)

GO

 

Use of function in From Clause: