SQL Tutorial

SQL Tutorial Hot

Tag it:

Articles Reviews Structured Query Language

Written by Bogdan V

Thursday, 14 September 2006

Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

Page 17 of 17

{mos_sb_discuss:29}

Displaying running totals is a common request, and there is no straightforward way to do so in SQL. The idea for using SQL to display running totals similar to that for displaying rank: first do a self-join, then, list out the results in order. Where as finding the rank requires doing a count on the number of records that's listed ahead of (and including) the record of interest, finding the running total requires summing the values for the records that's listed ahead of (and including) the record of interest.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Running_Total
Greg	50	50
Sophia	40	90
Stella	20	110
Jeff	20	130
Jennifer	15	145
John	10	155

The combination of the WHERE clause and the ORDER BY clause ensure that the proper running totals are tabulated when there are duplicate values.

SQL Percent To Total

To display percent to total in SQL, we want to leverage the ideas we used for rank/running total plus subquery. Different from what we saw in the SQL Subquery section, here we want to use the subquery as part of the SELECT. Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,

SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Pct_To_Total
Greg	50	0.3226
Sophia	40	0.2581
Stella	20>	0.1290
Jeff	20	0.1290
Jennifer	15	0.0968
John	10	0.0645

The subquery "SELECT SUM(Sales) FROM Total_Sales" calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row.

SQL Cumulative Percent To Total

To display cumulative percent to total in SQL, we use the same idea as we saw in the Percent To Total section. The difference is that we want the cumulative percent to total, not the percentage contribution of each individual row. Let's use the following example to illuatrate:

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,

SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Pct_To_Total
Greg	50	0.3226
Sophia	40	0.5806
Stella	20	0.7097
Jeff	20	0.8387
Jennifer	15	0.9355
John	10	1.0000

The subquery "SELECT SUM(Sales) FROM Total_Sales" calculates the sum. We can then divide the running total, "SUM(a2.Sales)", by this sum to obtain the cumulative percent to total for each row.

User reviews

There are no user reviews for this item.

Add new review

<< Prev - Next

Last Updated ( Saturday, 30 June 2007 )

< Prev

[ Back ]