In this example which runs here live
I would like to add the user id in the retrieved data
I use this
;with postvotes as ( select v.creationdate, v.userid , p.id postid , case v.votetypeid when 1 then 'accepts' when 2 then 'up-votes' when 3 then 'down-votes' when 9 then 'bounty_recieved' end vote_type , case p.posttypeid when 1 then 'question' when 2 then 'answer' end post_type , sum(case v.votetypeid when 1 then 15 when 2 then 10 when 3 then -2 when 9 then bountyamount end ) reputation_change from votes v inner join posts p on p.id = v.postid where p.owneruserid = ##userid?1719510## and posttypeid in (1,2) and votetypeid in (1,2,3,9) group by v.creationdate, p.id, votetypeid, posttypeid ), bounties as ( select v.creationdate , v.postid postid , 'bounty-offered' vote_type , 'question' post_type , sum(bountyamount) reputation_change from votes v where v.userid = ##userid?1719510## and votetypeid = 8 group by v.creationdate, v.postid, votetypeid ), approvededits as ( select se.creationdate , se.postid postid , 'approved-edit' vote_type , case p.posttypeid when 1 then 'question' when 2 then 'answer' end post_type , sum(2) reputation_change from suggestededits se inner join posts p on p.id = se.postid where se.Owneruserid = ##userid?1719510## and approvaldate is not null group by se.creationdate, se.postid, posttypeid ) select * from postvotes union select * from bounties union select * from approvededits
But I receive this error: Column ‘votes.UserId’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I fix it?
Advertisement
Answer
You forgot to add userid in your group by list. So your updated query might look like –
;with postvotes as ( select v.creationdate, , p.id postid , case v.votetypeid when 1 then 'accepts' when 2 then 'up-votes' when 3 then 'down-votes' when 9 then 'bounty_recieved' end vote_type , case p.posttypeid when 1 then 'question' when 2 then 'answer' end post_type , sum(case v.votetypeid when 1 then 15 when 2 then 10 when 3 then -2 when 9 then bountyamount end ) reputation_change from votes v inner join posts p on p.id = v.postid where p.owneruserid = ##userid?1719510## and posttypeid in (1,2) and votetypeid in (1,2,3,9) group by v.creationdate, p.id, votetypeid, posttypeid ), bounties as ( select v.creationdate , v.postid postid , 'bounty-offered' vote_type , 'question' post_type , sum(bountyamount) reputation_change from votes v where v.userid = ##userid?1719510## and votetypeid = 8 group by v.creationdate, v.postid, votetypeid ), approvededits as ( select se.creationdate , se.postid postid , 'approved-edit' vote_type , case p.posttypeid when 1 then 'question' when 2 then 'answer' end post_type , sum(2) reputation_change from suggestededits se inner join posts p on p.id = se.postid where se.Owneruserid = ##userid?1719510## and approvaldate is not null group by se.creationdate, se.postid, posttypeid ) select * from postvotes union select * from bounties union select * from approvededits