SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I keep a runing total in SQL? thanks

    How do I keep a runing total?
    I have a form that users use to sign in.
    It's only a single table with six columes. This table will be used to track the number of people that sign up to for an event some kind of RSVP stuff.
    Here is the table:
    -----------------------------------
    CREATE TABLE [dbo].[evite] (
    [empId] [int] IDENTITY (1, 1) NOT NULL ,
    [fname] [varchar] (25) NOT NULL ,
    [lname] [varchar] (25) NOT NULL ,
    [adults] [int] NOT NULL ,
    [under_five] [int] NULL ,
    [five_eight] [int] NULL ,
    [nine_thirteen] [int] NULL
    ) ON [PRIMARY]
    GO
    ------------------------------------------
    What I want to do is keep a running total. That is, when each invitee register, the total increments. So I can easily look up the total number of Adults, under_five, five_eight etc, that will be attending.
    thanks

    I am using MS SQL 7. on W2k and ASP
    assigned.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A trigger maybe?
    When table A is updated, or a new row inserted, update table B

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's only one table that contains all the required fields.
    assigned

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One table?
    And all rows store the same total number of people?!
    Why?

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You just run a query:
    SELECT SUM( under_five ) as under_five,
    SUM( five_eight ) as five_eight,
    SUM( nine_thirteen ) as nine_thirteen
    FROM evite

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this is a test of a larger application, but if it works on one table, it should work.
    Just one table with adults for one row, children for another etc. The idea is to keep a running total of potential attendees.
    thanks
    assigned

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This sound like what I need!
    On another topic, why don't you like Mysql? The issues you raised on the link are mostly addressed in V4.00?
    By the way, does Sybase have a GUI tool to visualize what I am doing if choose to use Sybase?
    I can do command line etc, but I also like tools that I can visualize.

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Assigned
    Just one table with adults for one row, children for another etc. ...
    I'm sorry, but I'm totally confused
    Could you please explain what the columns adults, under_five etc are used for, show some example of values?

    Does each row contain info about someone who signs up for event, and brings x adults, y under five etc?

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, an adult logs in by entering his or her firstname, then a lastname.
    Then on another form field, how many children under 5,
    then another field 9-12 etc.
    The idea is to keep a tally of each age group that will be attending.
    I did not see any need to have a different table for children since children can not attend without an adult and the names of the children is not being gathered. Only the number that each adult will be bringing to the party.
    Thanks
    assigned

  10. #10
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works perfectely.
    Thanks
    assigned

  11. #11
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, now it's clearer to me

    New question; the user enters the number of people (s)he will bring to the party, but where is the ID of the party in the table evite?

    And what if the same person signs up for another event?
    Am I completely wrong if I think that you should have at least three tables?

    * people
    p_id
    firstname
    lastname

    * events
    e_id
    date
    etc...

    * people_signed_up_for_events
    p_id (fk)
    e_id (fk)
    adults
    under_five
    five_eight
    nine_thirteen

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...and with the tables above, you could have the sum of adults, under_five etc in events (and updates in events triggered by inserts/updates in people_signed_up_for_events)

  13. #13
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are right and I thought about doing just that but when I read the requirements (I did not write it) it clearly says it's only one party (a weding). Since you can only have one weding at a time on any given day for any one person, I just put the attendees in one table and since the kids can only attend if their parents do and their names (kids) is not recorded, it did not make much sense to me gathering data that will not be used. So the event id should've been named wedindId??
    I am wrong?
    Thanks
    assigned

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Assigned
    ...only have one wedding at a time on any given day for any one person...
    Yep, but the person (with firstname & lastname) in the table evite (or, in my model, people) isn't the bride/bridegroom - correct? So, you still need a reference to the event/wedding.

    And there will be more than one event/wedding in the db I guess?

    And, no, you don't have to record the names of the children etc, that's why the people_signed_up_for_events table still has the columns adults, under_five etc. The table people will only contain the "commander" for each group of attendees


Bookmarks

Posting Permissions

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