This is a new question based on the solution to my previous question

I have the following tables:


int (pk) user_id
date last_visit
int (fk) local_site


int (pk) site_id


int (fk) site_id
int total_users
int monthly_users
int weekly_users

And I am using this query to update the user_stats table

UPDATE site_stats
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, 
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

This works fine, except that if there are 0 users linked to a site in the time frame then no update will occur, so the table col will keep the previous value rather than showing 0.

What would be the best way to add a condition so that any sites with 0 linked users is updated with 0?

My first guess was to use a CASE in the WHERE clause?

1 Answer 1


Either you run an additional UPDATE (best in one transaction):

UPDATE site_stats s
SET    total_users   = 0
     , monthly_users = 0
     , weekly_users  = 0
WHERE  NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id)

Or use this instead (I would do that):

UPDATE site_stats s
SET    total_users   = COALESCE(u.total, 0)
     , monthly_users = COALESCE(u.monthly, 0)
     , weekly_users  = COALESCE(u.weekly, 0)
FROM   site_stats s0
   SELECT local_site 
        , count(*) AS total
        , count(last_visit >= now()::date - 30 OR NULL) AS monthly
        , count(last_visit >= now()::date - 7  OR NULL) AS weekly
   FROM   users
   GROUP  BY 1
   ) u ON u.local_site = s0.site_id
WHERE  s.site_id = s0.site_id;

Including some other minor simplifications.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.