Running total in SQL

CREATE TABLE #Employee(Id INT IDENTITY,Name VARCHAR(100), Salary money)
 
INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

SELECT *, SUM(Salary) OVER(ORDER BY Id) AS 'Running Total' FROM #Employee

 

SELECT e1.*,(SELECT SUM(Salary) FROM #Employee e2 WHERE e1.id >= e2.id) AS 'Running Total' 

FROM #Employee e1

--Note - For Running total, required column each row value must be unique. In OVER () clause or WHERE clause.

Ranking functions in sql

 CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

SELECT
       Salary
       , ROW_NUMBER() OVER(ORDER BY Salary ASC) AS 'ROWNUMBER'
       , RANK() OVER(ORDER BY Salary ASC) AS 'RANK'
       , DENSE_RANK() OVER(ORDER BY Salary ASC) AS 'DENSERANK'
       , ROW_NUMBER() OVER(PARTITION BY Salary ORDER BY Salary ASC) AS 'PARTITIONBY'
       , NTILE(3) OVER(ORDER BY Salary) AS 'NTILE'

 FROM #Employee

1. ROW_NUMBER() - Serial Number for each row.
2. RANK() - Rank each row and same record assign same rank and next rank number will be addition of the previous rank (same rank).
3. DENSE_RANK() - Rank each row and same record assign same rank and next rank number would be assigned the next rank value.
4. NTILE(3) - Groups the row based on number given.

Select 2nd highest salary in sql

CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

--2nd highest

SELECT MAX(Salary) FROM #Employee WHERE Salary < (SELECT MAX(Salary) FROM #Employee)

 

--N number highest using subquery
--Change highlighted color number to get the N number of highest
SELECT TOP 1 *
FROM (
       SELECT DISTINCT TOP 2 Salary
       FROM #Employee
       ORDER BY Salary DESC
) A
ORDER BY Salary ASC
 
--OR
 
SELECT DISTINCT Salary
FROM #Employee Emp1
WHERE 2 = (
       SELECT COUNT(DISTINCT Emp2.Salary)
       FROM #Employee Emp2
       WHERE Emp2.Salary >= Emp1.Salary

)

 

--N number highest using CTE

--Change highlighted color number to get the N number of highest
;WITH RESULT
AS
(
       SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS 'DENSERANK'
       FROM #Employee
)

SELECT TOP 1 * FROM RESULT WHERE DENSERANK = 2 

--OFFSET

--Change highlighted color number to get the N number of highest
SELECT DISTINCT Salary
FROM #Employee
ORDER BY Salary DESC
OFFSET 2-1 ROWS

FETCH NEXT 1 ROWS ONLY