European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server Hosting - HostForLIFE.eu :: How To Find Running Total And Final Total In SQL Server?

clock September 11, 2019 10:04 by author Peter

Here, we will explain how to find Running Total and Final Total or Grand Total in SQL Server. For example, you have the below given #tblEmployee temporary table and has the data, as shown below.

    CREATE TABLE #tblEmployee 
    ( 
       EmpID int identity, 
       Salary Decimal(8,2), 
       Grade varchar(10) 
    ) 


Insert some values in a temporary table like this.
    INSERT INTO #tblEmployee(Salary,Grade) Values(10000.51,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(20000.12,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(25000.02,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(9000.28,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(7000,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(6000,'C') 


Find the Running Total in the SQL Server

Write the following SQL Query to find the running total for the Salary column from #tblEmployee temporary table.
SELECT EmpID, Grade, Emp.Salary ,(SELECT SUM(Salary) FROM #tblEmployee WHERE EmpID <= Emp.EmpID) 'Running Total' FROM #tblEmployee Emp 
I have used the sub-query to find the running total.
 
Find the Final Total or Grand Total in SQL Server
 Write the following SQL Query to find the final total or grand total for the Salary column from #tblEmployee temporary table.
    SELECT EmpID, SUM(Salary) AS Salary FROM #tblEmployee GROUP BY EmpID WITH ROLLUP 

Here I used ROLLUP to achieve the desired result.
 
What is ROLLUP
The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
 
Following Result produce by this SQL Query,

So, in this blog, we tried to explain how to find the running total from the SQL table. I hope this blog helps all of us. It is also most asked question from the interview.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



SQL Server Hosting - HostForLIFE.eu :: Instead Of Triggers

clock September 4, 2019 12:41 by author Peter

Instead of triggers are used to skip DML commands. They fire when you try to execute insert, update or delete statement but instead of executing these commands trigger actually works and trigger functionality executes.
 

Example
    create table approved_emp ( eid int identity(1,1), ename varchar(30)) 
    create table emp ( id int identity(1,1) , ename varchar(30), AddedBy varchar(30)) 
      
    Create trigger instead_of on approved_emp 
    instead of insert 
    as 
    begin 
    declare @name varchar(30) 
    select @name=ename from inserted 
    insert into temp_audit values(@name, USER ) 
    end 


So, basically, trigger will work as, when we will try to add new record in approved_emp table, instead of inserting new records it will add ename into emp table. No data will reflect in approved_emp table as trigger is fired on the table every time while adding data into that table.
 
You can also create instead of triggers for update and delete as well.
 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in