[FIXED] How to count existing field combinations in SQLIte

Issue

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;

Answered By - Tim Biegeleisen

Answer Checked By - Willingham (PHPFixing Volunteer)