Aggregate and Summarize Totals with SQL SUM

In this we will discuss about ‘aggregate and summarize totals with SQL SUM function.  Knowing the method to calculate the complete addition of a specified column in a SQL Server database table is crucial. For instance, a sales supervisor may require the overall sales figure of each salesperson per month or a continuous addition of sales of a customer for a particular month. What is the solution to these issues?

Let’s discuss Solution

Microsoft provides assistance to SQL database developers in the form of the SUM function, which can be used to address various issues. In this article, we will focus on three key objectives:

• Calculating the sum of a single column.
• Generating a cumulative total, and
• Substituting a convoluted pivot statement with aggregated sums.

Normally, I consult the internet-based records whenever I encounter new T-SQL functions that I wish to utilize. After that, I investigate the function’s capabilities and limitations. Solely numerical values are compatible with the SUM function. To determine which data types generate results with the most frequently used functions, namely SUM, AVG, MIN, and MAX, we will analyze the “Order” table in the “NorthWND” database.

The below SQL commands shows us that only the MIN and MAX functions are supported by the date/time data type

SELECT
      MAX([RequiredDate]) AS RequiredDate,
      MIN([ShippedDate]) AS ShippedDate
     
  FROM [NorthWND].[dbo].[Orders]

Next, we will explore the string data format. The following code indicates that exclusively MIN and MAX functions are recognized, whereas SUM and AVG will result in an error message of an invalid data type.

SELECT
      MAX([ShipCity]) AS ShipCity,
      MIN([ShipRegion]) AS ShipRegion
     
  FROM [NorthWND].[dbo].[Orders]

But if we will use SUM instead of MAX and MIN then it will be following error.

Simple Summation with SQL SUM

The SUM function assumes the ALL keyword as default if not specified. However, if you include the DISTINCT keyword, the function will first calculate a distinct list of values for the column and then perform the summation.

SELECT 
  SUM(DISTINCT UnitPrice) As UnitPrice,
  SUM(ALL SalesAmount) As SAmount
FROM 
  Products
GO

The T-SQL code above demonstrates the difference between ALL and DISTINCT. The image below shows the different totals by the given keyword.

Aggregated Summation

It gets more interesting when we use these functions with the GROUP BY clause. A business user might want to know the total sales by month during 2011. The T-SQL snippet produces that output.

SELECT 
  OrderBYMonth,
  SUM(SalesAmount) AS Sales_Amt
FROM 
  Orders
GROUP BY
  OrderBYMonth
ORDER BY
  OrderBYMonth
GO

Let’s get Running Totals

The PARTITION BY clause establishes a segment in a query result set, and the window operation calculates a result for each record. Operations can be utilized to obtain summarized values such as continuous averages, cumulative aggregates, running sums, or the highest N per category results.

The T-SQL query provided below generates a view called “SalesByRegion2023” by using a SELECT statement that computes a running sum per country, year, month, and sales order number. As PARTITION BY only accepts one column, we are combining the country, year, and month to create a single hash key. The ROW_NUMBER function assigns a unique number to each row in the partition.

CREATE OR ALTER VIEW RunningTotal2011
AS
SELECT 
  SalesTerritoryCountry,
  OrderYearMonth,
  SalesOrderNumber,
  ROW_NUMBER() OVER ( PARTITION BY (SalesTerritoryCountry + OrderYearMonth) ORDER BY SalesOrderNumber) AS RegionDateRow,
  SUM(SalesAmount) OVER ( PARTITION BY (SalesTerritoryCountry + OrderYearMonth) ORDER BY 
SalesOrderNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RegionDateTotal
FROM 
  SalesByRegion2023
GO

Since there are over 5,000 rows, we want to filter the data for sales in INDIA in January 2023. The query below performs this filtering and returns the top 100 rows

SELECT 
  TOP 10 * 
FROM 
  SalesByRegion2023

The SQL SUM function was created to total numeric values. We saw that date/time and string values result in errors from the algebraic parser. Today, we looked at both simple and aggregated summations. Remember, by default, you are summing ALL values. You can add the DISTINCT keyword if necessary.

We hope after reading this article you’ll able to use this in your daily development practices. For similar details you can also check out Microsoft Docs. For more such articles you can visit our SQL Section