Query to get top 5 users by total month sales amount

Question

I was hoping you could help me figure this out. I would like to run a query that would search a table and all it's rows and the date matches the current month. Then return the user_id. What I am trying to achieve is gathering the top 5 user id's under this query.

How can I change this to find the top 5 for the current month? I am usually able to figure out my queries but this one has me stumped.

This is all I have at the moment.

$top = mysqli_query($mysqli, "SELECT sum(amount) AS total FROM sales WHERE status = 'S' AND MONTH(date) = 1");

    while($row = mysqli_fetch_row($top))
         {

           $userid = $row['0'];

         }

Update:

$top = mysqli_query($mysqli, "SELECT user_id
    FROM
    (
        SELECT user_id, SUM(amount) total 
        FROM sales 
        WHERE status = 'S' 
        AND MONTH(date) = MONTH(CURDATE())
        AND YEAR(date)  =  YEAR(CURDATE())
        GROUP BY user_id
    ) q
        ORDER BY total DESC
        LIMIT 5");

           while($row = mysqli_fetch_row($top))
                {
                   $topsales[] = $row['0'];
                }

    foreach($topsales as $topsale) {
            echo $topsale;
    }

Answer 1

Are you looking for a query like this?

SELECT user_id
  FROM
(
  SELECT user_id, SUM(amount) total 
    FROM sales 
   WHERE status = 'S' 
     AND MONTH(date) = MONTH(CURDATE())
     AND YEAR(date)  =  YEAR(CURDATE())
  GROUP BY user_id
) q
 ORDER BY total DESC
 LIMIT 5

or just

SELECT user_id 
  FROM sales 
 WHERE status = 'S' 
   AND MONTH(date) = MONTH(CURDATE())
   AND YEAR(date)  =  YEAR(CURDATE())
 GROUP BY user_id
 ORDER BY SUM(amount) DESC
 LIMIT 5

Here is SLQFiddle demo

Note: using MONTH() and YEAR() functions will prevent MySql from using any index you might have on date column

---

Now your php code might look like this

$db  = new mysqli('localhost', 'user', 'password', 'dbname');
if ($db->connect_errno) {
    die('Cannot connect'); // TODO: better error handling
}
$sql = "SELECT user_id 
          FROM sales 
         WHERE status = 'S' 
           AND MONTH(date) = MONTH(CURDATE())
           AND YEAR(date)  =  YEAR(CURDATE())
         GROUP BY user_id
         ORDER BY SUM(amount) DESC
         LIMIT 5";

$topsales = array();

if ($query = $db->prepare($sql)) {
    $query->execute();
    $query->bind_result($user_id);

    while ($query->fetch()) {
        $topsales[] = $user_id;
    }
    $query->close();
} else {
    die('Unable to prepare: ' . $db->error); // TODO: better error handling
}
$db->close();

foreach ($topsales as $topsale) {
    echo $topsale . '<br>';
}