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

Answers


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.


Need Your Help

When adding view inside layout and when removing and re-adding IllegalStateException

android layout view android-linearlayout relativelayout

When adding view inside layout and when removing and re-adding, it throws like this.