CODEWITHSUNDEEP
mysqlsql
Mark
Mark

Reputation: 678

IDs from a Select statement with GROUP BY and HAVING

I'm looking to select (or delete) all the IDs that are included in the following SQL...

SELECT ID 
FROM AHsTransactions
GROUP BY TypeID, AH1ID, AH2ID
HAVING COUNT(*) > 1

The question is how to get the ID from the rows that are within this grouping?

A simple dataset would be: INSERT INTO AHsTransactions(TypeID, AH1ID, AH2ID) VALUES (1, 2, 3), (1, 2, 3), (1, 2, 4)

I've got it working using multiple SQL statements, but isn't this possible in one SQL statement??

1) SELECT TypeID, AH1ID, AH2ID FROM AHsTransactions GROUP BY TypeID, AH1ID, AH2ID HAVING COUNT(ID) > 1

Then I go through the returned rows and delete (or select ID )...

2) DELETE FROM AHsTransactions WHERE TypeID=$row[$i]['TypeID'] AND AH1ID=$row[$i]['AH1ID'] AND AH2ID=$row[$i]['AH2ID']

Upvotes: 0

Views: 976

Answers (2)

Brave Soul
Brave Soul

Reputation: 3620

Add Id in the GROUP BY clause, it is compulsory to add every column in group by which is present in select statement:

SELECT ID 
FROM AHsTransactions
GROUP BY ID, TypeID, AH1ID, AH2ID
HAVING COUNT(*) > 1

Upvotes: 1

Chris Farmer
Chris Farmer

Reputation: 25396

You must also group by id. That's what the error message is telling you. Add it to your group by list. In general, if it's in your select list, you must also group by it.

Upvotes: 0

Related Questions