SQL Server running totals (sum over order by) -

SQL Server running totals (sum over order by) -

i looking "running totals" sql server. easy query. wrote this:

select firstname, lastname, thetot, sum(thetot) on (order lastname) runningtot customers

you'd think accurate running total output. not true. here's spit out:

firstname lastname thetot runningtot billy bobthornton 0.01 4.46 billy bobthornton 4.45 4.46 bob hope 3.52 7.98 jimmy johnson 4.84 12.82 jason meyers 3.50 16.32 ted turner 1.77 18.09

is me or should first record's running 0.01?

the self reply provided drastically changes semantics.

in question calculating running total ordered firstname, arbitrarily switching order pk brings resultset answers exclusively different question.

the reason resultset seeing documented in books online

if rows/range not specified order specified, range unbounded preceding , current row used default window frame.

the semantics of range values tied same order by value included in each other's window frame.

one way around compatible original semantics add together tie breaker order by ensures uniqueness - , ties can never occur.

select firstname, lastname, thetot, sum(thetot) on (order lastname, pk) runningtot customers

however not best performing method.

as discussed in aaron bertrand's running total comparison implementation of range less performing rows.

moreover rows behaves expecting anyway.

select firstname, lastname, thetot, sum(thetot) on (order lastname rows unbounded preceding) runningtot customers

would return

| firstname | lastname | thetot | runningtot | |-----------|-------------|--------|------------| | billy | bobthornton | 0.01 | 0.01 | | billy | bobthornton | 4.45 | 4.46 | | bob | hope | 3.52 | 7.98 | | jimmy | johnson | 4.84 | 12.82 | | jason | meyers | 3.5 | 16.32 | | ted | turner | 1.77 | 18.09 |

or possibly

| firstname | lastname | thetot | runningtot | |-----------|-------------|--------|------------| | billy | bobthornton | 4.45 | 4.45 | | billy | bobthornton | 0.01 | 4.46 | | bob | hope | 3.52 | 7.98 | | jimmy | johnson | 4.84 | 12.82 | | jason | meyers | 3.5 | 16.32 | | ted | turner | 1.77 | 18.09 |

anyway (it undeterministic row appear first in presence of ties)

sql-server sum