Find SQL records which have identical values in two fields

04/11/11

Permalink 12:12:18 pm, by sarneil, 91 words, 372 views   English (CA)
Categories: Activity Log; Mins. worked: 60

Find SQL records which have identical values in two fields

I had to find all instances of criminals in which both the given-name and surname fields are identical and the number of such duplicates

Here's the SQL I used for the example:

SELECT `surname`, `given_name`, COUNT(`surname`) AS Instances FROM criminals GROUP BY `surname`, `given_name` HAVING ( COUNT(`surname`) > 1 ) AND ( COUNT(`given_name`) > 1)

and here's the same SQL with abstracted table and field names:

SELECT `field1Name`, `field2Name`, COUNT(`field1Name`) AS Instances FROM tableName GROUP BY `field1Name`, `field2Name` HAVING ( COUNT(`field1Name`) > 1 ) AND ( COUNT(`field2Name`) > 1)

Pingbacks:

No Pingbacks for this post yet...

Capital Trials at the Old Bailey

Simon Devereaux has approximately 10,000 records of people convicted in potentially capital cases between 1710 and 1840 in London heard at the Old Bailey court. This project will create a web-based database which will allow interested researchers and members of the public to compose queries on that data (e.g. women charged with robbery 1710-1720). It must be able to support a range of queries and produce output allowing researchers to identify trends in judicial practice over that time.

Reports

Categories

September 2014
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30        

XML Feeds