Multiple duplicates finding

Question

I have a table like this

ticket_id   destination
---------   -----------
5           Paris
5           Paris
5           California
5           Paris
6           Nebraska
15          London
6           Nebraska
6           Nebraska

I would like to select all ticket ids that are in the table over 2 times and are to the same destination.

So, in this case, I would obtain a table like:

ticket_id   destination num_times
---------   ----------- ---------
5           Paris       3
6           Nebraska    3

I know how to obtain all tickets that are repeated certain times:

SELECT ticket_id, destination,
COUNT(ticket_id) AS num_times
FROM table
GROUP BY ticket_id
HAVING ( COUNT(*) > 2 )

However I am not sure how I can add the destination similarity.

Answer 1

Incidentally, your query doesn't need to be quite so big. Here's a briefer version that works:

SELECT ticket_id, destination, COUNT(*) AS num_times
FROM table
GROUP BY 1, 2
HAVING COUNT(*) > 2

Mantra: LESS == GOOD

Answer 2

Just group by both ticket id and destination:

SELECT ticket_id, destination,
 COUNT(ticket_id) AS num_times
FROM table
GROUP BY ticket_id, destination
HAVING ( COUNT(*) > 2 )