Expression Snippets

SUM COUNT AVG

Basic calculation example; used with Microsoft Adventureworks DB.

SELECT
 Department,
 SUM(Salary) AS TotalSalary,
 COUNT(*) AS TotalCount,
 AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department WITH ROLLUP
HAVING Department IN ('Marketing','Engineering')
;

SUM Multiple Row Values

Example usage: Sum multiple rows of values by a grouped identifier; i.e. AccountID with several rows of a percentage value and you need one summed total value.

SELECT
 field1, -- Distinct identify field to group by, ex. [account_id]
 EXP(SUM(LOG([field to be summed])))-1 AS summed_field
FROM [dbo].[sourcetable]
GROUP BY field1
;

Example Use

CREATE TABLE [dbo].[stage_accumulatedvalue] (
	field1 char(15),
	summed_field decimal(10,4)
)
;

/**************************************************************************/
/***STEP X:	  Stage Accumulated Value Calculation Result by field1
***/
/**************************************************************************/

INSERT INTO [dbo].[stage_accumulatedvalue] (
	field1,
	summed_field
)

SELECT
 field1, -- Distinct identify field to group by, ex. [account_id]
 EXP(SUM(LOG([field to be summed])))-1 AS summed_field
FROM [dbo].[sourcetable]
GROUP BY field1
;