Solved

MySQL Using SUM(X) and >= to compare results and show highest only

Posted on 2009-05-08
15
423 Views
Last Modified: 2013-12-12
I have a mySQL database with four tables and a site that calls data from them and displays it in dynamic html tables.
The database records players scores at various venues, and I need to develop a query that will get only the players who have cumulative points of over 750 in any one venue, if a player has more than 750 points in two or more venues then it only shows the highest of these points totals and the venue name they were scored at.

So if Bob has 5 games in venue A totaling 650 points and 7 games in venue B totalling 800 points only venue Bs points total and venue name are shown..hope that makes sense.

The below code show a query I have at the moment that totals all players for a single venue, this works fine, but I can't ammend it to do what I now need.

I Think I need to add something like AND SUM(Position.Points) >= '750' but that wont work and even if it did it would return all points over 750 not the highest over 750 for each player.

I hope that makes sense, I'm new to sql and databases in general so if you can help I'd really appreciate it.

SELECT SUM(Position.Points) , Player.FirstName, Player.LastName, COUNT(Results.MembershipNo)
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
GROUP BY Player.MembershipNo
ORDER BY SUM(Position.Points) DESC

Open in new window

0
Comment
Question by:DrEvilAces
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 30 total points
ID: 24339470
I'm not sure if I understand your requirements, but try using a HAVING clause:
SELECT Player.MembershipNo, Player.FirstName, Player.LastName, 
  SUM(Position.Points), COUNT(Results.MembershipNo)
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
GROUP BY Player.MembershipNo,Player.FirstName, Player.LastName
HAVING SUM(Position.Points) >= 750
ORDER BY SUM(Position.Points) DESC

Open in new window

0
 
LVL 2

Expert Comment

by:shoppedude
ID: 24339486
How are you joining the Position table to the Player table?  If multiple MembershipNo records have the same Position value, how do you know which Points value belongs to which MembershipNo?

-Lee
0
 
LVL 1

Author Comment

by:DrEvilAces
ID: 24339879
cxr - what you suggests works in as much as it limits the records to only those over 750 which is great, but it is still combining the total points for all venues, I need to find a way for the query to basically do as yours does for each venue, compare the results for each player and only show the venue that eahh player has the most points in.
The current query returns the 1st position player as having 2575 points when in fact he has 1800 points in one venue and only 375 points in the other. there fore I only want the 1800 points to show along with the name of that venue.

Shoppedude - I probably should have named the position table Points as all it does is store points for each position, I'll try to explain...
Player table has: MembershipNo, FirstName, LastName, Town, Email
Results table has: ResultID, VenueID, membershipNo, Date, Position
Position Table has: Position, Points
Venue has: VenueID, VenueName, adress, contact info etc etc.

So Player.MembershipNo links with Results.MembershipNo, and Results.Position links with Position.Position. So if a membership number appears in both tables (Player & Results) the corresponding number of Points is returned.
I don't know if this set up is correct, but it's worked for me so far (www.gbpokerclub.co.uk goto leagues > player lounge to see current tables in action)
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 14

Expert Comment

by:racek
ID: 24339887

SELECT pl.MembershipNo, pl.FirstName, pl.LastName, 
  SUM(po.Points) AS pos_points, COUNT(re.MembershipNo) AS count_results
FROM Player pl  
join Results re on pl.MembershipNo = re.MembershipNo 
join  Position po on re.Position = po.Position
join Venue ve on ve.VenueID = re.VenueID
GROUP BY 1,2,3
HAVING pos_points >= 750
ORDER BY pos_points DESC

Open in new window

0
 
LVL 14

Assisted Solution

by:racek
racek earned 25 total points
ID: 24339895

SELECT pl.MembershipNo, pl.FirstName, pl.LastName, ve.VenueID
  SUM(po.Points) AS pos_points, COUNT(re.MembershipNo) AS count_results
FROM Player pl  
join Results re on pl.MembershipNo = re.MembershipNo 
join  Position po on re.Position = po.Position
join Venue ve on ve.VenueID = re.VenueID
GROUP BY 1,2,3,4
HAVING pos_points >= 750
ORDER BY pos_points DESC

Open in new window

0
 
LVL 1

Author Comment

by:DrEvilAces
ID: 24340042
racek - your first suggestion seems to work in a similar way to cxr's but adds a membership number column.
However, the second version you posted was one comma away for pure genius (I found the missing comma after SELECT .... ve.VenueID)
I also changed VenueID to VenueName but it works exactely how I need it to.
Thanks to all of you for your input I'm off to disect it and understand it a bit better.
Cheers
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24340090
DrEvilAces, when multiple experts help you, you should split the points. In this case racek was obviously building on the sugegstion provided by me. (I added the MembershipNo column and the HAVING clause).
0
 
LVL 1

Author Comment

by:DrEvilAces
ID: 24340539
crx - I aplogies for that this is the first question I have ever asked here and was a little bamboozeled by all the voting and scoring.
Your right of course I should have split the points - if there is a way to change it let me know and i happily will.
Your input was extremely helpfull, and although I stuffed up the points you have my most sincere thanks.
0
 
LVL 2

Accepted Solution

by:
shoppedude earned 70 total points
ID: 24340547
I would suggest that the Accepted Answer does not provide the results you request.  In my testing, I have found that it will report a person more than once if they have more than 750 points at more than one venue and will not report a person if they have more than 750 total points but not at any one venue.  The code below, though more complicated, solves both of these issues, including the requirement to show the number of points aggregated at a particular venue.

-Lee


SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueID AS Venue,
  SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
 
FROM Player,
 (SELECT Player1.MembershipNo, Venue1.VenueID, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
    FROM Player Player1, Results Results1, Position Position1, Venue Venue1
   WHERE Player1.MembershipNo = Results1.MembershipNo 
     AND Results1.Position = Position1.Position
     AND Venue1.VenueID = Results1.VenueID
   GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
 (SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
    FROM Player Player3, Results Results3, Position Position3
   WHERE Player3.MembershipNo = Results3.MembershipNo 
     AND Results3.Position = Position3.Position
   GROUP BY Player3.MembershipNo) SubSelect3
 
WHERE Player.MembershipNo = SubSelect.MembershipNo
  AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
                              FROM (SELECT Player2.MembershipNo, Venue2.VenueID, SUM(Position2.Points) AS sum_points2
                                      FROM Player Player2, Results Results2, Position Position2, Venue Venue2
                                     WHERE Player2.MembershipNo = Results2.MembershipNo
                                       AND Results2.Position = Position2.Position
                                       AND Venue2.VenueID = Results2.VenueID
                                     GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
                             WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
  AND Player.MembershipNo = SubSelect3.MembershipNo
  AND SubSelect3.sum_points3 >= 750
 
ORDER BY SubSelect.sum_points DESC

Open in new window

0
 
LVL 1

Author Comment

by:DrEvilAces
ID: 24340652
Ah crap your absolutely right - I was so pleased thinking I had the solution I didn't read the results properly. Your absolutely correct in that it reports the same person more than once when they score 750+ from multiple venues.

However, the second point you make "will not report a person if they have more than 750 total points but not at any one venue"  is fine as the points are not transferrable between venues. So only those venues which have 750+ points accumulated at them should show.

In otherwords the first error you spotted is indeed an error, the second is not.
What would I need to change to fix the first problem but leave the second bit as it was, that bit of code is waayyy beyond my level of understanding at the moment and I don't want to 'break' it by mucking around! I will read it thoroughly to try to grasp it.

Is there a way to re-open this question and re-allocate points? Seems I jumped the gun here!!
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24340664
DrEvilAces: no worries, things like this happens all the time, I just wanted to make you aware of the right way to assign points when multiple experts are involved. :)

You can re-open this question by clicking the Request Attention button in the lower right corner of the original question. A moderator will open it for you.
0
 
LVL 1

Author Comment

by:DrEvilAces
ID: 24340836
OK by switching line 27 from:
AND SubSelect3.sum_points3 >= 750
 to
AND SubSelect.sum_points >= 750
It now only shows people who have scored more than 750 in any one venue and appears to correctly only show the highest scoring venue (if over 750)

I think this is the final answer!!
SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueID AS Venue,
  SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
 
FROM Player,
 (SELECT Player1.MembershipNo, Venue1.VenueID, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
    FROM Player Player1, Results Results1, Position Position1, Venue Venue1
   WHERE Player1.MembershipNo = Results1.MembershipNo 
     AND Results1.Position = Position1.Position
     AND Venue1.VenueID = Results1.VenueID
   GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
 (SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
    FROM Player Player3, Results Results3, Position Position3
   WHERE Player3.MembershipNo = Results3.MembershipNo 
     AND Results3.Position = Position3.Position
   GROUP BY Player3.MembershipNo) SubSelect3
 
WHERE Player.MembershipNo = SubSelect.MembershipNo
  AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
                              FROM (SELECT Player2.MembershipNo, Venue2.VenueID, SUM(Position2.Points) AS sum_points2
                                      FROM Player Player2, Results Results2, Position Position2, Venue Venue2
                                     WHERE Player2.MembershipNo = Results2.MembershipNo
                                       AND Results2.Position = Position2.Position
                                       AND Venue2.VenueID = Results2.VenueID
                                     GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
                             WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
  AND Player.MembershipNo = SubSelect3.MembershipNo
  AND SubSelect.sum_points >= 750
 
ORDER BY SubSelect.sum_points DESC

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24342741
I agree with cxr .. there are several experts contributing to the solution
0
 
LVL 1

Author Closing Comment

by:DrEvilAces
ID: 31579602
I have split the points this time and have given Shoppedude a bigger share as his suggestion was the most accurate and complete.
Thanks to you all for your help and patience!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question