Jump to content


Photo

Trouble with rows


  • Please log in to reply
2 replies to this topic

#1 theINTERN

theINTERN
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 15 August 2006 - 05:40 PM

Due to a poor database design, I'm having a dilemma with creating a chart of information on a webpage.  I'm going to try my best to be descriptive as possible. 

I have a table that holds information for events that people organize. My primary key is an auto-incremented program ID.  I need to create a page that displays the event holder's name along with the total points they have earned for holding each event, which I would have to do a SUM() for.  An example of the table would look like this, each row divided by a comma:

PID, NAME, EVENTNAME, POINTS
1, Alex, Pizza party, 50
2, Alex, Ice cream social, 100
3, Judy, Pizza party, 75
4, Jane, Movie night, 30

I need the webpage to display as follows:

Alex, 150
Judy, 75
Jane, 30

The page I need to create needs to list each person in the table along with a total of points that they have earned.  The problem I'm having is only naming Alex once, and adding up his points.  I figured the code snippet would look like something along the lines of:

SELECT NAME as name, SUM(POINTS) as points FROM TABLE;

Well obviously that won't work because of not being able to distinguish between names and who the points belong to.  So, I'm here to ask anyone if they have a solution to this problem.  I am a beginner when it comes to Oracle SQL and any help would be much appreciated. I am working in ColdFusion.

If I haven't been descriptive enough I will do my best to explain.  Thanks.

#2 countnikon

countnikon
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 30 August 2006 - 06:50 PM

I would add another field in there that is a unique userID.  That would solve the problem real quick if you don't have very many records yet.

#3 syed

syed
  • Members
  • PipPipPip
  • Advanced Member
  • 151 posts
  • LocationEngland

Posted 05 September 2006 - 06:19 PM

Just to add to what countnikon is saying, basically, you need a unique ID for the user, this could be an auto number although in database design, you would not set an auto number in the database, thats done in a database tier. Any way, all you would then do is query each ID, this will result in just a particular persons records being shown, you can then add the points togther using a loop. The problem is if you have a lot of users, then querying each user ID to calculate their points could become a big processing job. Hope that helps.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users