Solved

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

Posted on 2009-05-08
15
424 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

829 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