Running totals on a select count statement

From: zack (anonymous_at_discussions.microsoft.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 06:11:06 -0800

Im trying to get a running total to work.

example that I found @ http://www.databasejournal.com/features/mssql/article.php/3112381 :
select OrderId, OrderDate, O.OrderAmt
        ,(select sum(OrderAmt) from Orders
                          where OrderID <= O.OrderID)
          'Running Total'
from Orders O

OrderId OrderDate OrderAmt Running Total
----------- -------------------- ---------- -------------
1 2003-10-11 08:00:00 10.50 10.50
2 2003-10-11 10:00:00 11.50 22.00
3 2003-10-11 12:00:00 1.25 23.25

What Im looking to do is something like this:
select OrderId, OrderDate, count(O.OrderAmt)
        ,(select sum(Count(OrderAmt)) from Orders
                          where OrderID <= O.OrderID)
          'Running Total'
from Orders O

I need the first select to count and then run a sum on that count.

Thanks for help in advance.

zack

Relevant Pages

  • Re: Clustered vs Non-Clustered
    ... > either leave OrderId as clustered ... > OrderNumber ... > couldn't that be set as the primary key ?. ... >> OrderDate smalldatetime NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: XML data to SQL Server with multiple orders and multiple order det
    ... SQLXML Bulk Load component from SQLXML 3.0. ... inserting the data using a ... OrderDate DATETIME NOT NULL ... --INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) ...
    (microsoft.public.sqlserver.xml)
  • Re: Running totals on a select count statement
    ... Since the sum of counts is really just another count, ... as RunningTotalOrderCount ... group by OrderID, OrderDate ...
    (microsoft.public.sqlserver.mseq)
  • Re: Go to specified row
    ... that some people will probably bash you for the notion of a row number in a ... tie-breaker, e.g., orderid. ... ORDER BY orderdate, orderid ... BG, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Add row if result has no records
    ... returned" row and use the following TOP 1 WITH TIES trick to select it ... OrderID, CustomerID, OrderDate ...
    (microsoft.public.sqlserver.programming)