How do I select records with duplicates in multiple columns in MS SQL/Excel syntax?-CodePudding

I have the following table:

[Sheet1$]

Group	Division	Name
555	2	Adam
555	1	Bob
444	1	Sarah
555	2	Ann
444	1	Jeff

I want to select all records from [Sheet1$] (I am working with Excel and ODBC) that have the same Group and Division, but multiple Names. I need all 3 columns in the result list.

Result

Group	Division	Name
555	2	Adam
555	2	Ann
444	1	Sarah
444	1	Jeff

I have the following query that results in an error:

SELECT [Group], [Division], [Name], COUNT(*)
FROM [Sheet1$]
GROUP BY [Group], [Division] HAVING COUNT(*)>1

The above query does not work because of one column not included in the GROUP BY clause from my understanding, but between my rusty SQL and ignorance of the MS/Excel SQL syntax, I can't seem to form a valid query for my need. How should I format the correct query?

CodePudding user response：

If you are applying a GROUP BY clause to your SQL statement, then all columns in the SELECT clause - that are not aggregated - must be included in the GROUP BY clause.

So, in your example, you basically have three options.

Add some kind of "aggregation" to the [Owner] column

SELECT [Joint Venture], [Division of Interest], MIN([Owner]), COUNT(*)
FROM [Sheet1$]
GROUP BY [Joint Venture], [Division of Interest] HAVING COUNT(*)>1

Add the [Owner] column to the GROUP BY clause (if you don't want to apply aggregation)

SELECT [Joint Venture], [Division of Interest], [Owner], COUNT(*)
FROM [Sheet1$]
GROUP BY [Joint Venture], [Division of Interest], [Owner] HAVING COUNT(*)>1

--or-- Remove the [Owner] column from the SELECT clause

SELECT [Joint Venture], [Division of Interest], COUNT(*)
FROM [Sheet1$]
GROUP BY [Joint Venture], [Division of Interest] HAVING COUNT(*)>1

CodePudding user response：

If you need the repeated rows with the individual owners, you'll have to do some in-place concatenation.

SELECT [Joint Venture], [Division of Interest], [Owner]
FROM [Sheet1$]
WHERE CONCAT([Joint Venture], [Division of Interest]) IN
(
   SELECT CONCAT([Joint Venture], [Division of Interest])
   FROM [Sheet1$]
   GROUP BY [Joint Venture], [Division of Interest]
   HAVING COUNT (*) > 1
)

This is assuming that the ODBC driver for Excel supports the CONCAT() method. If it doesn't, you might try [Joint Venture] [Division of Interest] in both places if CONCAT() doesn't work.