Re: Running totals on a select count statement

From: Steve Kass (skass_at_drew.edu)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 13:43:34 -0500

Zack,

  Since the sum of counts is really just another count, maybe this will
work:

  select
    OrderId,
    OrderDate,
    count(OrderAmt) as OrderCount,
    (select count(T2.OrderAmt)
     from T T2
     where T2.OrderID <= T1.OrderID
    ) as RunningTotalOrderCount
  from T T1
  group by OrderID, OrderDate

SK

zack wrote:

>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: 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)
  • Clustered vs Non-Clustered
    ... whether OrderID or OrderNumber will be queried the most. ... couldn't that be set as the primary key ?. ... > OrderDate smalldatetime NOT NULL, ... > ProductID int IDENTITY PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)