CODEWITHSUNDEEP
sqlms-accessgroup-byduplicates
dovid
dovid

Reputation: 6471

Group By a match between several fields

I have a table like:

|    id    |  field1  |  field2  |  field3  |    
---------------------------------------------
|    1     |     A    |     B    |    C     |
|    2     |     E    |     F    |    G     |
|    3     |     H    |     A    |    I     |
|    4     |  *null*  |     J    |    K     |
|    5     |     C    |     L    |    M     |
---------------------------------------------

now I want to group to find duplicates. I write this query:

SELECT FIRST(ID), COUNT(id) 
FROM MyTable
GROUP BY field1
HAVING COUNT(id) > 1

this returns duplicates in field1 only. If I write GROUP BY field1, field2, field3 It returns duplicates across three fields. But I want to find duplicates from any field and group the result set. In other words, such a result:

|   Count  |  Value |
---------------------
|    2     |    A   |
|    2     |    C   |
---------------------

Is it possible in a simple group-by? The values are non fixed-width, and can be null.

Upvotes: 1

Views: 42

Answers (1)

Gustav
Gustav

Reputation: 55906

You can create a union query:

SELECT Field1 AS FieldValue FROM MyTable
UNION ALL
SELECT Field2 AS FieldValue FROM MyTable
UNION ALL
SELECT Field3 AS FieldValue FROM MyTable

Now use this query as source in a query similar to your original.

Upvotes: 1

Related Questions