lundi 4 juillet 2016

Syntax error in Stack Exchange Data Explorer query (maybe caused by HAVING clause)?

I wanted to enter this query into the SE Data Explorer, but it tells me "Incorrect syntax near the keyword GROUP":

SELECT TOP 100
  U.Id as [User Link],
  COUNT(P.Id) as "# of Questions",
  DATEDIFF(DAY, MIN(U.CreationDate), GETDATE()) as "Account age (days)",
  ROUND(
    CAST(COUNT(P.Id) AS FLOAT) /
    NULLIF(CAST(DATEDIFF(DAY, MIN(U.CreationDate), GETDATE()) AS FLOAT), 0),
    3)
    as "Q per day"

FROM Users U
JOIN Posts P on U.Id = P.OwnerUserId

WHERE P.PostTypeId = 1
HAVING COUNT(P.Id) > 10

GROUP BY U.Id
ORDER BY 4 DESC

I started to get this error after having added the HAVING COUNT(P.Id) > 10 line to filter users with too few questions.

My aim is to list the top askers of a site, their question count, their account age in days and their average questions per day rate.

Aucun commentaire:

Enregistrer un commentaire