Calculate event scores

[KitCarl]

I need to calculate points for placements in an event based on the number of entrants. 1st is a multiple of 1, 2nd is a multiple of 1/2, 3rd is a multiple of 1/3, fourth is a multiple of 1/4.

 

I have an person table (person_id | person_name) , event table (event_id | event_name | number_of_entrants ) and a placement table (event_id | person_id | placement(integer))

 

How do I create a result that would give the proper score for each place 1st - 4th ?

[fenway]

I don't follow -- you have the correct ordering already?

[KitCarl]

Example data for this table

 

event_id | person_id | placement(integer)

 

1|34|1

1|23|2

1|53|4

1|12|3

2|117|1

 

My thought was to record the actual place in the table and calculate the points based on a factor of the number_of_entries field when I create the report. This way if I find an error in the entries number and need to change it, score will automatically recalculate when the report is run. I just have no idea how to create a query to calculate the scores, or if this is better done via PHP? If you could get me started or let me know if I should use a different method it will be much appreciated.

 

[fenway]

You mean just an expresion based on placement?

[KitCarl]

Not sure of the terminology, but I need to be able to return a result where as an example if the number_of_entrants is 48 for the event, it will return a score of 48 for 1st(placement=1), 24 for 2nd(placement=2), 16 for 3rd(placement=3) and 12 for 4th(placement=4)

 

Guessing something like below, but I don't have actual tables to test yet. Score is to two decimal places and is not rounded.

 

SELECT person.person_name AS Name, placement.placement AS Place, TRUNCATE((event.number_of_entrants/placement.placement), 2) AS Score
FROM event
JOIN placement ON event.event_id = placement.event_id
JOIN person ON person.person_id = placement.person_id
Where event.event_id = ?

 

And just as an extra twist 5 places are awarded but 5th does not receive points? How do I make the score 0 instead of placement/5 for this?

[fenway]

Well, with a CASE statement, you can do anything.

[KitCarl]

Well, with a CASE statement, you can do anything.

 

I'm sure you can if you understand them, but after reading the MYSQL doc's I haven't a clue how it would work for my need. That last select statement was already pushing the envelope of my MYSQL knowledge.

 

If someone could please show me how to use them with the query in the post above I'd appreciate it. I find it much easier to grasp a new subject if I can tinker with a working model to see how/why it works.

 

C'mon Fenway, can I please, please get more than a short sentence. 

[fenway]

case when placement < 5 then number_of_entrants/placement when placement = 5 then 0 else 0 end

[KitCarl]

Thank You!!!