Solved

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

Posted on 2009-05-08
15
421 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …
The viewer will learn how to dynamically set the form action using jQuery.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now