PHP查询以获取每个用户在日期之间的金额之和

Question

我有一个数据库设置，它保存了一个销售人员的数据谁已经收集了一定的钱，并有他的佣金，以及那里。

 Example:
 Date        Name    Amount Sold   Commission Earned
 2013-2-3   John A     100.00          20.00
 2013-2-3   Amy W      200.00          40.00
 2013-2-3   John A     230.00          46.00
 2013-2-2   John A     300.00          60.00
 2013-2-2   Bill B     400.00          80.00
 Etc....

我正试着用整个星期的佣金向最顶尖的销售人员展示一下。我需要以某种方式查询数据库，以找到每周的所有佣金，然后对它们进行分组和汇总，并显示该周的最高收入者。

这是我到目前为止所得到的成果，而且效果很好，但它显示了上周所有的收入。我只是不知道如何把他们按名字分组，然后把佣金加在一起，然后从最赚钱的销售人员那里展示。

   require_once('connectvars.php');
   $dbc = mysqli_connect(CDB_HOST, CDB_USER, CDB_PASSWORD, CDB_NAME);
   // GET COLLECTOR INFO
   $query = "SELECT * FROM commissioninfo WHERE thedate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY 
   AND thedate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY ORDER BY amountoffeeearned DESC"; 
   $result = mysqli_query($dbc, $query);

     while ($row = mysqli_fetch_array($result)) {
         echo '<tr>';
         echo '<td>' . $row['salesperson'] . '</td>';
         echo ' <td>' . $row['amountoffeeearned'] . '</td>';
         echo '</tr>';                              
      }
      mysqli_close($dbc);

Answer 1

只需在Name中添加Group by，然后Sum Amount Sold，您将得到每个user的Amount Sold之和。就像这样。

SELECT   NAME, 
         Sum(`Amount Sold`)     sum_amount , 
         sum(`commission earned`) sum_comm 
FROM     commissioninfo 
WHERE    thedate >= curdate() - interval dayofweek(curdate())+6 day 
AND      thedate < curdate()  - interval dayofweek(curdate())-1 day 
GROUP BY NAME 
ORDER BY sum_amount DESC

最新情况：

SELECT salesperson, 
       Sum(amountcollected) 
FROM   commissioninfo 
WHERE  thedate >= Curdate() - INTERVAL Dayofweek(Curdate())+6 day 
       AND thedate < Curdate() - INTERVAL Dayofweek(Curdate())-1 day 
GROUP  BY salesperson 

Answer 2

按DESC的顺序在组中添加名称和SUM(佣金所得)。

或

试试这个：

SELECT Name,
       SUM([Amount Sold]) as WeekAmountSold,
       SUM([Commission Earned]) as WeekCommissionEarned 
FROM commissioninfo 
WHERE Date >= DATE_ADD(curdate(), INTERVAL(1-DAYOFWEEK(curdate())-7) DAY) 
      AND Date < DATE_ADD(curdate(), INTERVAL(1-DAYOFWEEK(curdate())) DAY) 
GROUP BY Name 
ORDER BY SUM([Commission Earned]) DESC