mysql - 对 mysql 中的字段进行计数或求和

Question

我选择开始日期和结束日期、订单状态，然后从我的表格中列出订单。

最后一列表示该日期有多少订单。我想得到一个数字，即总共有多少订单。我的意思是:从 2017-01-01 到 2017-01-31 有 127 个订单。

如果我们使用我的图片，结果将是 49 个订单。

SELECT datum, COUNT(rendeles_id) AS ennyi FROM rendeles_adatok 

                            WHERE datum >= '$date_start' AND (datum <= '$date_end' AND status = $rendeles_allapot)

                        GROUP BY datum ORDER BY $kereses_sorrend

完整代码

$date_start = mysqli_real_escape_string($kapcs, $_POST['date_start']);
                $date_end = mysqli_real_escape_string($kapcs, $_POST['date_end']);
                $rendeles_allapot = mysqli_real_escape_string($kapcs, $_POST['rendeles_allapot']);
                $kereses_sorrend = mysqli_real_escape_string($kapcs, $_POST['kereses_sorrend']);

                if($rendeles_allapot != 1234 )
                {
                    $sql = "
                        SELECT datum, COUNT(rendeles_id) AS ennyi FROM rendeles_adatok 

                            WHERE datum >= '$date_start' AND (datum <= '$date_end' AND status = $rendeles_allapot)

                        GROUP BY datum ORDER BY $kereses_sorrend
                   ";
                }
                else
                {
                    $sql = "
                        SELECT datum, COUNT(rendeles_id) AS ennyi FROM rendeles_adatok 

                            WHERE datum >= '$date_start' AND datum <= '$date_end'

                        GROUP BY datum ORDER BY datum ASC
                   ";
                }
               //printr($sql);

               echo '<table class="table table-hover table-bordered list">';
                echo '<thead>
                      <tr>
                        <td class="left">Kezdő dátum</td>                           
                        <td class="left">Záró dátum</td>
                        <td class="center">Vásárlások száma</td>
                      </tr>
                    </thead>';
                echo '<tbody>';
               $hirek_down = mysqli_query($kapcs, $sql) or die(mysqli_error($kapcs));
               $talalatok = mysqli_num_rows($hirek_down);
               $fetch_count = mysqli_fetch_assoc($hirek_down);
               if($talalatok > 0 )
               {
                   echo '<p>Összesen '.$fetch_count['ennyi'].' találat.</p>';
                   echo '<p>Total sum: </p>';
                   while($a = mysqli_fetch_assoc($hirek_down))
                    {
                        echo '<tr>
                                <td class="left">'.html($a['datum']).'</td>
                                <td class="left">'.html($a['datum']).'</td>
                                <td class="center">'.html($a['ennyi']).'</td>
                             </tr>';
                    }
                   echo '</tbody>';
                   echo '</table>';
               }

Answer 1

您必须使用 SUM() 来获取该列的总和。

SELECT datum, SUM(rendeles_id) AS ennyi FROM rendeles_adatok 

                        WHERE datum >= '$date_start' AND (datum <= '$date_end' AND status = $rendeles_allapot)

                    GROUP BY datum ORDER BY $kereses_sorrend

COUNT() 只会计算与您的搜索匹配的行数，而 SUM() 会添加指定列的值。