dataexplorer - SQL for bucketing counts -
i'm trying create histogram of people's reputations ask questions on site using stackexchange data explorer.
the following gives error message:
each group expression must contain @ least 1 column not outer reference. invalid column name 'lt_100'. ... suggestions appreciated
select case when reputation < 100 "lt_100" when reputation >= 100 , reputation < 200 "100_199" when reputation >= 200 , reputation < 300 "200_299" when reputation >= 300 , reputation < 400 "300_399" when reputation >= 400 , reputation < 500 "400_499" when reputation >= 500 , reputation < 600 "500_599" when reputation >= 600 , reputation < 700 "600_699" when reputation >= 700 , reputation < 800 "700_799" when reputation >= 800 , reputation < 900 "800_899" when reputation >= 900 , reputation < 1000 "900_999" else "over 1000" end reputationrange, count(*) totalwithinrange users join posts on users.id = posts.owneruserid join posttags on posttags.postid = posts.id join tags on tags.id = posttags.tagid posttypeid = 1 , posts.creationdate > '9/1/2010' group 1
you should use single-quotes ranges being classified. if use " " treated column name. should include calculation in group by clause.
select case when reputation < 100 'lt_100' when reputation >= 100 , reputation < 200 '100_199' when reputation >= 200 , reputation < 300 '200_299' when reputation >= 300 , reputation < 400 '300_399' when reputation >= 400 , reputation < 500 '400_499' when reputation >= 500 , reputation < 600 '500_599' when reputation >= 600 , reputation < 700 '600_699' when reputation >= 700 , reputation < 800 '700_799' when reputation >= 800 , reputation < 900 '800_899' when reputation >= 900 , reputation < 1000 '900_999' else 'over 1000' end reputationrange, count(*) totalwithinrange users join posts on users.id = posts.owneruserid join posttags on posttags.postid = posts.id join tags on tags.id = posttags.tagid posttypeid = 1 , posts.creationdate > '9/1/2010' group case when reputation < 100 'lt_100' when reputation >= 100 , reputation < 200 '100_199' when reputation >= 200 , reputation < 300 '200_299' when reputation >= 300 , reputation < 400 '300_399' when reputation >= 400 , reputation < 500 '400_499' when reputation >= 500 , reputation < 600 '500_599' when reputation >= 600 , reputation < 700 '600_699' when reputation >= 700 , reputation < 800 '700_799' when reputation >= 800 , reputation < 900 '800_899' when reputation >= 900 , reputation < 1000 '900_999' else 'over 1000' end
Comments
Post a Comment