Question 20650 in Mysql Question posted in
The official MySQL documentation can be found here.

Select IDs with two or more entries greater than amount – Mysql

I’m new to MySQL, and I’m struggling with a homework problem:

  1. Get the name and initials of each player who has incurred two or more penalties of more than $40. Hint: use subquery that has an aggregate

I can get MySQL to show me the amounts more than $40, but I can’t seem to get it to only show the players that have two or more penalties. I’ve watched a ton of tutorials and read forums online, and after two hours, I’m finally waiving the white flag and asking for help on this one.

Here is the table:

Paymentno, playerno, payment_date, amount
'1','6','1980-12-08','100.00'
'2','44','1981-05-05','75.00'
'3','27','1983-09-10','100.00'
'4','104','1984-12-08','50.00'
'5','44','1980-12-08','25.00'
'6','8','1980-12-08','25.00'
'7','44','1982-12-30','30.00'
'8','27','1984-11-12','75.00'

Here is my code thus far:

SELECT DISTINCT playerno
FROM penalties 
WHERE amount > 40 IN (SELECT playerno
            FROM penalties
            GROUP BY playerno
            HAVING COUNT(DISTINCT playerno) > 1);

And here it was it returns:

Playerno
'6'
'44'
'27'
'104'
'8'

It should only return playerno 27, and I’ve tried all sorts of different variations. Can someone please point me in the right direction on this one? I appreciate your help and guidance!

Tags:

2

Answers


  1. 0

    We can use conditional aggregation here:

    SELECT playerno
FROM penalties
GROUP BY playerno
HAVING SUM(amount > 40) >= 2;

    If you must solve this using a subquery, I can suggest the following:

    SELECT DISTINCT playerno
FROM penalties p1
WHERE amount > 40 AND
      EXISTS (SELECT 1
              FROM penalties p2
              WHERE p2.playerno = p1.playerno AND
                    p2.paymentno <> p1.paymentno AND
                    p2.amount > 40);

    The second query finds all players having a penalty greater than 40 for whom we can also find a second record from a different occasion where the penalty also happens to be more than 40.

    Login or Signup to reply.
  2. 0

    As you have mentioned about; use of a subquery with an aggregate;

    Here is a solution.

    SELECT playerno
FROM penalties
WHERE playerno IN (
  SELECT playerno
  FROM penalties
  WHERE amount > 40
  GROUP BY playerno
  HAVING COUNT(*) > 1
)
group by playerno;

    I have tested it and here is a SQLFIDDLE for your reference.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search