Of Interest
Business Intelligence and Analytics
Oracle Training
|
Sybase
»
Transact-SQL
»
Tips Tricks
»
Self-Joins in SQL
Below are two SQL statements which can be used for "row numbering" and calculating "cumulative totals". The below versions use self joins to achieve the same.
Thanks to Amit Vidyasagar for this submission.
use pubs2
go
--Numbering Rows
SELECT t1.title_id, "row number" = count(*)
FROM titles t1, titles t2
WHERE t2.title_id <= t1.title_id
GROUP BY t1.title_id
go
--Cumulative Totals
SELECT t1.title_id, t1.advance, "cumulative_total" = SUM(t2.advance)
FROM titles t1, titles t2
WHERE t2.title_id <= t1.title_id
GROUP BY t1.title_id, t1.advance
go
|
|
|
Get the latest Rocket99 news and tech tips via
|
