Duplicate values in a column (template, runs on WORLD)

ANYRead onlyScripts Gallery

A data quality check to run on WORLD, then adapt: the values of a column that should be unique but appear several times. On WORLD, the names shared by several cities.

Verification status. Written from the BroadSQL documentation and the ANY documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Before adding a unique constraint.
After an import or a migration.

The Script

Download duplicate-values-check.sql

-- @description: Values that appear more often than expected in a column (on WORLD: city names; adapt CITY and NAME)
-- @tags: data quality, duplicates, template, world
-- @status: draft
-- @access: read
-- @dialect: ANY
-- @param min_count INTEGER = 1 "Values that appear more than this number of times are listed"
-- On WORLD, it lists the names shared by several cities. For your own data, replace CITY with your table and NAME
-- with the column that should hold unique values (TRIM removes the padding of a fixed-length CHAR column).
SELECT TRIM(NAME) AS NAME, COUNT(*) AS OCCURRENCES
  FROM CITY
 WHERE NAME IS NOT NULL
 GROUP BY TRIM(NAME)
HAVING COUNT(*) > ${min_count}
 ORDER BY OCCURRENCES DESC, NAME;

Parameters

Parameter	Type	Default	Meaning
`min_count`	`INTEGER`	`1`	Values that appear more than this number of times are listed

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/duplicate-values-check.sql.
Open the sample with CONNECT WORLD; and run it as it is. For your own data, copy the file and replace CITY and NAME with your table and column.
Check what it declares: LIB SHOW gallery/duplicate-values-check.sql;
Run it: @gallery/duplicate-values-check.sql min_count=2;

To keep the result in a file: DUMP LIB gallery/duplicate-values-check.sql TO duplicates AS XLSX; (see Exporting Script results).

What you get

Each duplicated value with the number of rows holding it, most frequent first; no row means no duplicate. On WORLD, min_count=2 lists 13 city names used three times, such as Cambridge, Hamilton and Valencia, and the default min_count=1 lists 65 names.

Limits

Table and column names cannot be parameters: ${name} is always a value. Edit them in the file.
NULL values are not counted as duplicates.
The values were computed on the WORLD sample with the H2 engine; the Script itself was not run through BroadSQL by the BroadSQL team.

Found something wrong or unclear on this page? Tell us, with the page address.