Calculate Final Total in SQL Server

In this article we will learn how to Calculate Final Total In SQL Server. Now a day it is very tricky question asked by interviewer for experienced or fresher.

Let’s start it with practically. Create a database.

CREATE DATABASE Demo

Now create a table, query for create a data table.

CREATE TABLE Employee

(

Id INT,

Name NVARCHAR (100),

Salary INT

)

Now press F5 for execute this query, after execute this query Employee table is created successfully.

Now insert records in this (Employee) table.

Insert into Employee values(1,'Rahul',22000), (2,'Raj',15000), (3, 'Aman',12000), (4,'Abhishek',9000)

Select above query and execute it. It means all these records are inserted in Employee table which you can see.

SELECT * FROM Employee

Employee Table Data

Now come on the point for Calculate Final Total.

Question: What do you mean by Calculate Final Total?

Answer: Calculate Final Total means sum all employee’s salary (Column) and show it in last row.

Before implement this in Sql query we will know about ROLLUP.

Question: What is ROLLUP?

Answer: ROLLUP operator is used for calculate subtotal and grand total for specific column while it’s group data. It is used with group by clause.

Note: If you use only ROLLUP then you will have to pass group by column name inside ROLLUP operator.

Now implement this logic there are two methods in SQL Server for implement it, query for it.

First Method:

SELECT ID, SUM (SALARY) AS 'Salary' FROM EMPLOYEE GROUP BY ROLLUP(ID)

Output:

Calculate Final Total 

Second Method:

SELECT ID, SUM (SALARY) AS 'Salary' FROM EMPLOYEE GROUP BY ID WITH ROLLUP  

Note: When we use WITH ROLLUP then we will have to pass column name after group by and the use WITH ROLLUP.

Calculate Final Total  

You can download it from youtube.

Download Calculate Final Total Salary In SQL Server Video


You can download from github.

Download Calculate Final Salary PPT Slide

Conclusion: In this article we saw that how to Calculate Final Total in Sql server in easy way using ROLLUP and WITH ROLLUP Operator, but ROLLUP and WITH ROLLUP provide the same output.