How to perform a function in SELECT on a subset of the data

I’ve got a query to return info on all the users on my site where people make posts about things:

SELECT users.ID, users.username, users.signupdate, users.lastlogin, users.hideonline, users.avatar, users.fbUID, COUNT( posts.ID ) AS postcount, SUM( up - down ) AS totalscore, ROUND( SUM( up - down ) / COUNT( posts.ID ) , 0 ) AS averagescore
FROM users
LEFT OUTER JOIN posts ON users.ID = posts.userID
WHERE posts.anon !=1
GROUP BY users.ID
HAVING COUNT( posts.ID ) >=10
ORDER BY averagescore DESC

I want to change it to also return the average score for only posts made in the last 30 days, but still also return the average score for ever post. The

ROUND( SUM( up - down ) / COUNT( posts.ID ) , 0 ) AS averagescore

obviously does it at the moment for all posts. I don’t know how to do this or if it’s even possible in one query.

To try and explain what I mean, if I was to do it separately this would be the query:

SELECT users.username , ROUND(SUM(up-down)/COUNT(posts.ID),0) AS monthaverage 
FROM users LEFT OUTER JOIN posts ON users.ID=posts.userID 
WHERE posts.anon != 1 AND posts.date >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)
GROUP BY users.ID HAVING COUNT(posts.ID) >= 10
ORDER BY monthaverage DESC

My question is, is there a way to combine those two queries to return ‘monthaverage’ in the first query along with everything else?

Thanks

You’re a genius! thanks :slight_smile:


select users.ID, 
       users.username, 
       users.signupdate, 
       users.lastlogin, 
       users.hideonline, 
       users.avatar, 
       users.fbUID, 
       count(posts.ID) AS postcount, 
       sum(up-down) AS totalscore, 
       round(sum(up-down)/count(posts.ID),0) as averagescore,
       (select round(sum(up-down)/case when count(*) = 0 then 1 else count(*) end,0)
          from posts 
         where users.ID = posts.userID 
           and posts.anon <> 1 
           and posts.date >= current_date - interval '30' day) AS monthaverage
  from users
  join posts 
    on users.ID = posts.userID
 where posts.anon <> 1
 group by users.ID
having count(posts.ID) >= 10
 order by averagescore desc