Aggregate function / Group By - invalid column error
Using classic ASP and MS-SQL server 2008
What I am trying to do is select duplicate records, and count them so I can then update the first one and delete the rest.
My query selects the duplicates OK but as soon as I try and introduce a count it stops working with the message "Column 'calendar.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
I have tried to rewrite this and added the count to the second SELECT and adding the id to GROUP BY (and as many combinations of this sort of thing as I can think of but I still get errors. The answers I have looked at on stackoverflow for a solution seem to be a little complicated and not really applicable (or I'm not looking for the right thing)
Where am I going wrong with this? (also is this the best method to look for and edit duplicates in a smallish table (200k rows?)
Dim strSQL_dup, rsSQL_dup, SQL_dupRecords, RecCount strSQL_dup = "SELECT id, COUNT(id) AS RecCount FROM calendar WHERE start_date IN ( SELECT start_date FROM calendar WHERE pId = '" & pId & "' GROUP BY start_date HAVING (COUNT(start_date ) > 1) ) " Set rsSQL_dup = conn.Execute(strSQL_dup) While Not rsSQL_dup.EOF If RecCount = 1 Then 'will eventually update the row response.write(rsSQL_dup("id")) ' id of first' Else 'will eventually delete the other rows response.write(rsSQL_dup("id")) ' id of subsiquet rows End If rsSQL_dup.MoveNext Wend
It seems you are missing GROUP BY in the main query:
SELECT id, COUNT(id) AS RecCount FROM calendar WHERE start_date IN (SELECT start_date FROM calendar WHERE pId = '" & pId & "' GROUP BY start_date HAVING (COUNT(start_date ) > 1) ) GROUP BY clalendar.id
(The last line can be of course GROUP BY id I just wanted to highlight the link between the last and first lines)
The issue is that COUNT is an aggregate function, so whatever argument you pass to it needs to be in your GROUP BY clause.
If id itself is your key to determine uniqueness, you can change your query to
SELECT id, COUNT(id) AS RecCount FROM calendar group by id having count(id) > 1
If the date matters for filtering a subset of your data e.g. to search in a particular period only, you can include it in a WHERE clause. On the other hand, if date and id together determine uniqueness, you will need to include both fields in your GROUP BY clause.