SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
-
Jun 18, 2003, 09:09 #1
- 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.
-
Jun 18, 2003, 09:58 #2
- 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
-
Jun 18, 2003, 10:48 #3
- 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
-
Jun 18, 2003, 11:00 #4
- 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?
-
Jun 18, 2003, 11:02 #5
- 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 eviteMatt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 18, 2003, 11:25 #6
- 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
-
Jun 18, 2003, 11:28 #7
- 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.
-
Jun 18, 2003, 11:39 #8
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by Assigned
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?
-
Jun 18, 2003, 11:48 #9
- 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
-
Jun 18, 2003, 11:48 #10
- Join Date
- Dec 2001
- Posts
- 181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Works perfectely.
Thanks
assigned
-
Jun 18, 2003, 11:58 #11
- 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
-
Jun 18, 2003, 12:00 #12
- 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)
-
Jun 18, 2003, 12:06 #13
- 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
-
Jun 18, 2003, 14:12 #14
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by Assigned
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