Results 1 to 4 of 4

Thread: Running Totals

  1. #1
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Red face Unanswered: Running Totals

    Hi Folks,

    I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......

    Have a table :

    create table test_registrations
    (
    date_maint smalldatetime,
    user_name1 varchar (255),
    user_address1 varchar (255),
    total_users int
    )
    go

    If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :

    select date_maint , count (1)
    from test_registrations
    group by date_maint
    order by date_maint desc
    go

    The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want
    the total users for the users on 1st to read 10 and total users on the 3rd to read 25.

    I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    select 
    	distinct convert(varchar(10),date_maint,101), 
    	(
    		select 
    			count(user_name1) 
    		from 
    			test_registrations 
    		where 
    			convert(varchar(10),date_maint,101) <= convert(varchar(10),b.date_maint,101)
    	) 
    from 
    	test_registrations b
    Try this ... am not sure whether it will work or not ... not near a server right now to test the query
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking Re: Running Totals

    Excellent mate. Thanks again. I knew it was a subquery.

    Cheers

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: Running Totals

    Is the example above a correlated subquery?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •