robgutsopedra commented on issue #6457: URL: https://github.com/apache/incubator-devlake/issues/6457#issuecomment-1827359423
Hello @Startrekzky ! Thanks a lot for your help! I have tried your suggestion, but I'm afraid it doesn't seem to work:  I tried to do some modifications and test it directly on the DB, and the last "GROUP_BY" failed because: SQL Error [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.pr_comment_number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by So, trying to be sueful, I finally went with: ``` with t1 as( select pull_request_id as pr_id, count(*) as pr_comment_number from pull_request_comments group by 1 ), t2 as( SELECT DATE_ADD(date(pr.created_date), INTERVAL -$interval(date(pr.created_date))+1 DAY) as times, pr.id as pr_id FROM pull_requests pr join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' WHERE $__timeFilter(pr.created_date) and pm.project_name in ($project) and pr.merged_date is not null ) -- select t2.*, t1.pr_comment_number FROM t2 left join t1 on t2.pr_id = t1.pr_id select t2.times, t1.pr_comment_number, t2.pr_id, sum(t1.pr_comment_number)/count(distinct t2.pr_id) as "PR Review Depth" FROM t2 LEFT JOIN t1 ON t2.pr_id = t1.pr_id GROUP BY t2.times, t2.pr_id, t1.pr_comment_number; ``` But I'm afraid is not working either:  Any suggestion is super welcomed! -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
