I'm using MySQL 5.7 and would like to calculate a percentage of edited posts for a user during a specific time.

Normally, I'd do this as two separate queries but I'm pretty sure could be done with a single query. I have a group of users who have edited posts and would like to get a count of the number of edited posts per user. Like so:

select count(*) as edited_post_count, 
       user_id 
from posts 
where edited_at between '2022-05-31' and '2022-06-06' 
group by user_id 
order by edited_post_count desc;

Now, I'd like to figure out the total number of posts they have all-time to be able to say 10 of 80 posts were edited during this time. edited_at would be null if they haven't edited (and yes should be last_edited_at). How could I add this to this query?

posts

CodePudding user response：

You can use CONCAT to output the COUNT of posts grouped by individual users within your date range concatenated with a subquery that gets the total posts where edited_at IS NOT NULL and the date range is not specified.

select 
  count(*) as edited_post_count,
  CONCAT(count(*), ' of ',  (SELECT COUNT(*) from posts a where a.user_id = b.user_id and edited_at is not null), ' posts were edited during this time.') as total_post_count,
  user_id 
from posts b 
where edited_at between '2022-05-31' and '2022-06-06' 
group by user_id 
order by edited_post_count desc;

Example Result:

If you want it as a %, divide your individual count by your total count multiplied by 100.

  (count(*)/(SELECT COUNT(*) from posts a where a.user_id = b.user_id and edited_at is not null))*100 as total_post_count_percent,

db<>fiddle here.