syntax error in Data Explorer

Question

The Stack Exchange Data Explorer allows SQL queries against a Stack Exchange database. I tried this one —

select
  month(CreationDate) month,
  year(CreationDate) year,
  sum(lower(left(Title,2))='wh')/count(*) wh,
  (select sum(Score)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_score,
  sum(Score)/count(*) score,
  (select sum(AnswerCount)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_answers,
  sum(AnswerCount)/count(*) answers
from Posts t
where PostTypeId=1 -- question
group by month,year;

— but the site told me

Incorrect syntax near ')'. Incorrect syntax near 'wh_score'. Incorrect syntax near 'wh_answers'.

and I cannot figure out why. Can anyone help, please?

Things I've tried, to no avail:

datepart(month,CreationDate) instead of month(CreationDate) (and likewise for year)
explicit as for aliases (then the latter two of the three errors complained about 'at' rather than about the aliases)
aliases that aren't built-in function names
left(Title,2) instead of lower(left(Title,2))
putting parentheses around the first two, and around the last two, of the four things joined by ands
explicit u. for column names in the subqueries

Followup: This is now at data.stackexchange.com/jewish%20life%20and%20learning/query/… — msh210
– msh210, Commented Apr 18, 2013 at 14:47

Cyril Gandon · Accepted Answer · 2013-04-18 07:19:16Z

1

You can't group by an alias, you need to specify the computed column
This is not allowed : sum(lower(left(Title,2))='wh'). You have to convert it into a CASE WHEN operator.

Here a corrected query (that gives a timeout):

select
    month(CreationDate) month
  , year(CreationDate) year
  , sum(case when lower(left(Title,2))='wh' then 1 else 0 end)/count(*) wh
  , (select sum(Score)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_score,
  sum(Score)/count(*) score,
  (select sum(AnswerCount)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_answers,
  sum(AnswerCount)/count(*) answers
from Posts t
where PostTypeId=1 -- question
group by month(CreationDate), year(CreationDate);

What are you trying to do with this query?

answered Apr 18, 2013 at 7:19

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

msh210 Over a year ago

Thank you! +1. It works now. (And doesn't timeout for me, but I'm doing it for a small SE site.) But it treats (or, at least, displays) every column in the result as an integer, so wh comes through (of course) as always 0. Do you know any way around that? (Of course, I can ask it separately.)

Cyril Gandon Over a year ago

Ask in a separate question. I don't understand what your are trying to do with this query, so I can't help you more.

msh210 Over a year ago

Okay, will do. (I'm trying to determine whether wh- questions are 'better' than other questions: specifically, whether they have more answers or net votes.)

Collectives™ on Stack Overflow

syntax error in Data Explorer

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related