SQL SERVER -- Aggregate Functions-- Part-18

                   By Using Aggregate Functions we can perform following operations Like SUM, MIN, MAX, AVG and COUNT.

SUM:- Used to get Total Salary of all the employees in a EMP Table.
MAX:- Used to get Maximum Salary of all the employees in a EMP Table.
MIN:- Used to get Minimum Salary of all the employees in a EMP Table.
AVG:- Used to get Average Salary of all the employees in a EMP Table.
COUNT:- Used to get Number of Employees in a EMP Table.

Sample Table Script :-

USE tempdb

GO

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya','01-12-2014','Bangalore','f',3,103)

-- Sample Table

SELECT * FROM EMP

-- SUM

SELECT SUM(SAL) AS Total_Salary FROM EMP

-- MAX

SELECT MAX(SAL) AS Total_Salary FROM EMP

-- MIN

SELECT MIN(SAL) AS Total_Salary FROM EMP

-- AVG

SELECT AVG(SAL) AS Total_Salary FROM EMP

-- COUNT
SELECT COUNT(SAL) AS Total_Salary FROM EMP

SQL SERVER -- Date Functions-- Part-17

                   By Using Date Functions we can extract different parts of dates like Year, Month and Day so.. on. And following are the date functions available in SQL Server.

GETDATE:-    Used to Display/Get system date.

-- GETDATE()

SELECT GETDATE() AS TODAY  

YEAR:-           Used to extract only year from the given date.

MONTH:-       Used to extract only Month from the given date.
DAY:-            Used to extract only Day from the given date.

SELECT GETDATE() AS SYSTEM_DATE

-- YEAR

SELECT YEAR(GETDATE()) AS YEAR

-- MONTH

SELECT MONTH(GETDATE()) AS MONTH

-- DAY

SELECT DAY(GETDATE()) AS DAY

DATEPART:-  Used to extract the part of the date from the       given date.

-- DATEPART

SELECT DATEPART(YY,GETDATE()) AS YEAR

SELECT DATEPART(MM,GETDATE()) AS MONTH

SELECT DATEPART(DD,GETDATE()) AS DAY

SELECT DATEPART(HH,GETDATE()) AS HOUR

SELECT DATEPART(DW,GETDATE()) AS DAY_OF_WEEK

SELECT DATEPART(WW,GETDATE()) AS WEEK_OF_YEAR

DATENAME:- Used to extract the relevant name of the part of the             given date.

-- DATENAME

SELECT DATENAME(YY,GETDATE()) AS YEAR

SELECT DATENAME(MM,GETDATE()) AS MONTH

SELECT DATENAME(DD,GETDATE()) AS DAY

SELECT DATENAME(HH,GETDATE()) AS HOUR

SELECT DATENAME(DW,GETDATE()) AS DAY_OF_WEEK

DATEDIFF:-  Used to Display/Get system date.

-- DATEDIFF

DECLARE @VALUE DATE

SET @VALUE='01-01-2013'

SELECT @VALUE

SELECT DATEDIFF(YY,@VALUE,GETDATE()) AS DIFF_IN_YEARS

SELECT DATEDIFF(MM,@VALUE,GETDATE())AS DIFF_IN_MONTHS

DIFFERENT FORMAT OF THE GIVEN DATES

SELECT GETDATE() AS SYSTEM_DATE

-- Different format of the given dates.

SELECT CONVERT(VARCHAR,GETDATE()) AS DEFAULT_FORMAT

SELECT CONVERT(VARCHAR,GETDATE(),100)

SELECT CONVERT(VARCHAR,GETDATE(),101) AS 'MM/DD/YYYY'

SELECT CONVERT(VARCHAR,GETDATE(),102) AS 'YYYY/MM/DD'

SELECT CONVERT(VARCHAR,GETDATE(),112) AS 'YYYY/MM/DD'

--

--

--

--

SELECT CONVERT(VARCHAR,GETDATE(),131) AS 'MM/DD/YYYY'

  

                             
              

SQL SERVER -- Built In Functions-- Part-16

                             
              Following are the Types of Built In Functions available in SQL SERVER. This is also called as System Defined Functions.
1.Date Functions

      GETDATE()
      YEAR()
      MONTH()
      DAY()
      WEEK()
      DATEPART()
      DATENAME()
      DATEDIFF()

2. Aggregate Functions

     SUM()

     MIN()

     MAX()

     COUNT()

     AVG()

3. Mathematical Functions

ABS()

CEILING()

FLOOR()

ROUND()

SQRT()

     SQUARE()
4. String Functions

SUBSTRING()

CHARINDEX()

REPLACE()

ISNULL()

STUFF()

LTRIM()

RTRIM()

COALESCE()

REVERSE()

RIGHT()

LEFT()

LOWER()

UPPER()

SPACE()

PATINDEX()

5. Conversion Functions

     CONVERT()

     CAST()

6. Ranking(Analytical) Functions

     ROW_NUMBER()

     RANK()

     DENSE_RANK()