#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Help on finding groups with a similar count as one groupmember on a certain column


    I have two tables:

    1: A table Person (P) which contains (among others) the column
    number

    2: A table Ticket (T) which contains (among others) the columns number and penalty.

    I need to make a query to find all persons with the same amount of tickets as a certain person. In other words, if the person with number x has y penalties, I need to find all other persons with y penalties.

    I already formulated the query beneath:

    SELECT P.number, COUNT(T.penalty) number_of_tickets
    FROM Person D, Ticket T
    WHERE T.number = P.number
    GROUP BY P.number

    I guess I need to use 'HAVING' but am not sure how to solve my query.

    Anyone can help a newbie?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Maybe a screenshot will make the question more clear (screenshot did not happen, do not know/ am not able to post it here. transcripted it in text...):

    ------------------------------------------------------------------------
    SELECT B.lidnummer, COUNT(B.betalingnummer) aantal_boetes
    FROM Boete B
    GROUP BY B.lidnummer

    DATA OUTPUT:

    lidnummer aantal_boetes
    130 1
    112 1
    109 2
    201 7
    208 9
    ------------------------------------------------------------------------
    My question is how I can extract the data with the same 'aantal_boetes' as 'lidnummer' 112.

    So 'lidnummer 112' has 'aantal_boetes 1'
    'lidnummer 130' also has 'aantal_boetes 1'

    So how can I extract those (and in such a way that if 'lidnumer 112' had a different count of 'aantal_boetes' it would automatically adjust).

    How would I change my query to get that info?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select lidnummer,
           count(*) aantal_boetes
      from Boete
     group
        by lidnummer
    having count(*) =
           (select count(*)
              from Boete
             where lidnummer = 112)
       and lidnummer <> 112

    Comments on this post

    • roynl agrees
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Thank you very much.

    Just starting to learn making queries and did not yet get to the concept of using a query into a query (as in a second SELECT, FROM, WHERE part).

    Thanks again!

IMN logo majestic logo threadwatch logo seochat tools logo