Re: Running totals on a select count statement

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

Date: Wed, 10 Mar 2004 11:46:08 -0800

Steve,

Im almost there but Im running into a problem with things being out of order. Here is what I have so far:
SELECT convert(Char(10), AR.Creation_Date, 101) AS DateWritten, COUNT(AR.Creation_Date) AS Written,
        (
                Select count(AR2.Creation_Date) from dbo.vw_Nothing AR2
                where convert(Char(10), AR2.Creation_Date, 101) <= convert(Char(10), AR.Creation_Date, 101)
        )
        as RunningTotal
FROM dbo.vw_Nothing AR
GROUP BY DATEPART(yy, AR.Creation_Date), DATEPART(mm, AR.Creation_Date), DATEPART(dd, AR.Creation_Date), CONVERT(Char(10), AR.Creation_Date, 101)

returns:
11/10/2003 1 32
11/11/2003 4 36
11/13/2003 1 37
12/18/2003 1 38
12/22/2003 1 39
12/23/2003 6 45
12/24/2003 2 47
12/30/2003 3 50
01/12/2004 9 9
01/29/2004 3 12
01/30/2004 1 13
02/06/2004 1 14
02/10/2004 3 17
02/24/2004 2 19
03/04/2004 11 30
03/05/2004 1 31

Whats happening is that the ordering is lost in the subquery and I can not specify an order by clause in the sub query. When I order the outside sql correctly the count is then incorrect. Any thoughts????? Possible a different date function.

Relevant Pages

  • Re: How do the records retrieve from data base when using sql statements?
    ... Just use an order by clause. ... guaranteed to be returned in the correct order in you don't specify an order ... SQL statement. ... This means that the keys are located continuously and every "insert" ...
    (comp.sys.ibm.as400.misc)
  • Re: how to write this?
    ... "Saurav" and I (Mark Brader) wrote: ... it is a science" is grammatically correct? ... an antecedent but also starts a subordinate clause like a conjunction. ... I am very much inclined to believe that this sentence is incorrect, ...
    (alt.usage.english)
  • Re: Create table and default order by clause
    ... by" clause to use when no "order by" clause is specified in a "select" query ... the only way to guarantee result order is to specify ORDER BY in the SELECT statement that selects from the table. ... Note that it is likely, but not guaranteed, that you will get results ordered in sequence by the table's clustered index in a trivial query with no joins or where clause. ...
    (comp.databases.ms-sqlserver)
  • Re: Using a Select Query to build a MDB table from a Fox table
    ... MS Access has a 'make-table' query with IN clause you can specify ... As you said the IN <database> clause is specific to Access SQL. ...
    (microsoft.public.fox.helpwanted)
  • Re: Specifying criteria as join condition vs WHERE clause
    ... clause will give you the same results. ... SELECT * FROM Employee E ... INNER JOIN Jobs J ... specify your intersection conditions in the JOIN ...
    (microsoft.public.sqlserver.programming)