More than one answer by the same user

Question

I hacked together this little query on the Data Explorer

The query finds questions where an answerer has posted two or more answers (which in itself is not necessarily a bad thing), it gives you the link to the question, information about which user posted the multiple answers, and how many answers by that user that was posted.

select question.Id as [Post Link], 
   answer.owneruserid as [User Link], 
   count(answer.id) as answers
from Posts question
join Posts answer on (answer.parentid = question.id)
where answer.OwnerUserId != 0
group by answer.OwnerUserId,
  question.id, answer.owneruserid
having count(answer.id) >= 2
order by answers desc

The last two group by clauses seem quite useless to me in a way, as they will always only be one unique version of them after grouping by answer.OwnerUserId. My experience from mySQL is that it simply picks a seemingly random value if not grouping by it, but in the SEDE I get this result:

Column 'Posts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

• Is there a way to remove these extra GROUP BY's? Or is adding these "the way it's supposed to be done"?
• How is this SQL overall, regarding performance, naming, structure, conventions and everything?
• Are there any alternative ways to rewrite this query?

Answer 1

General MySQL/MSSQL comparison

In regards to the rules for aggregate functions... you have count(answer.id) as one of your select values. As a consequence, you need a group by.

The group by must consist of every value that is used as part of any non-aggregate select value, or as part of the order-by.

MySQL's handling for this is 'wrong', and SQLServer is 'right'. I have seen MySQL do this, but, in reality, any system with non-deterministic behaviour is broken, and there is no way to make it deterministic unless you do the group-by right.

Columns in the Group By

You only need two columns in the group-by, question.Id, and answers.OwnerUserId

You have three columns because you have the answers.OwnerUserId twice... which is pointless.

Other suggestions

• You should, in general, use the same capitalization as the database schema for your tables/columns. Even if the database is case-insensitive.
• You should use all the value predicates you can when running SQL queries. For example, in your code, even though the only place where the conditions answer.ParentId = questionid and answer.OwnerUserID != 0 happens for questions, and answers, you should also add the predicates question.PostTypeId = 1 and answer.PostTypeId = 2 (because that is what really makes a question a question, and an answer an answer).

You should bookmark the SEDE Schema documentation...

Answer 2

You don't need to perform any join at all.

SELECT ParentId AS [Post Link]
     , OwnerUserId AS [User Link]
     , COUNT(Id) AS Answers
    FROM Posts
    WHERE
        ParentId IS NOT NULL           -- ← Answers only
        AND OwnerUserId <> 0
    GROUP BY ParentId, OwnerUserId
    HAVING Count(id) > 1
    ORDER BY Answers DESC;

Your instinct probably tells you that you need a JOIN to obtain the question titles. However, in Stack Exchange Data Explorer, there is a special feature: provide a PostId in a column named [Post Link], and it will take care of the presentation for you (implicitly doing a JOIN).