In a table I have two fields, an aircraft type code and a serial number. I’d like to count how many times a combination of these exist in the table. As aircraft sometimes get reregistered doubling is unavoidable. Is there a method that I can use to see how many times combinations of the same field combinations are present?
The query (if possible) should produce a list of some fields and the number of occurences the combination is present in the table.
Can this be done?
>Solution :
A simple aggregation query should do here:
SELECT aircraft_code, serial_num, COUNT(*) AS cnt
FROM yourTable
GROUP BY aircraft_code, serial_num;
But this would return all combinations, including those which only appear once. If you instead want to flag combinations which appear in duplicate, we can add a HAVING clause:
SELECT aircraft_code, serial_num, COUNT(*) AS cnt
FROM yourTable
GROUP BY aircraft_code, serial_num
HAVING COUNT(*) > 1;