order by dates that match criteria

[deangelo321]

I have the following query that outputs the users that received the highest number of favorites in the past week in descending order:

SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name
FROM users
INNER JOIN faves ON faves.user_id= users.id
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= created_on
GROUP BY id ORDER BY topFaves DESC

I would like to be able to extend this list to contain all users, not just from the past week, but still order them by the same criteria (the number of favorites they got in the past week and not by the number of faves they have in total).
I tried to include a subquery in the select but didnt have any luck with it.

Thanks in advance for any help

[Dan Grossman]

SELECT
  COUNT(faves.user_id) AS topFaves,
  faves.created_on,
  users.user_name
FROM 
  users
LEFT OUTER JOIN
  faves
ON
  faves.user_id = users.id
AND
  faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY
  users.id
ORDER BY 
  topFaves DESC

[deangelo321]

Great! thanks, Dan

[r937]

dan, your query is fine, except for the created_on column in the SELECT clause, which will have an arbitrary value

it will of course conform to the AND condition, but you won't know which of the faves it came from

[deangelo321]

Right now the query returns the "topFaves" alias which is the number of faves the user got this week. What if I also want to return the number of faves that user got ever? Will I need a different query for that or can I add it to this one?

[r937]

SELECT users.user_name
     , COUNT(CASE WHEN faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
                  THEN 'latest' ELSE NULL END) AS latestFaves
     , COUNT(faves.created_on) AS totalFaves
  FROM users
LEFT OUTER 
  JOIN faves
    ON faves.user_id = users.id
GROUP 
    BY users.id
ORDER 
    BY topFaves DESC

[deangelo321]

SELECT users.user_name
     , COUNT(CASE WHEN faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
                  THEN 'latest' ELSE NULL END) AS latestFaves
     , COUNT(faves.created_on) AS totalFaves
  FROM users
LEFT OUTER 
  JOIN faves
    ON faves.user_id = users.id
GROUP 
    BY users.id
ORDER 
    BY topFaves DESC

Many thanks. works like a charm