CODEWITHSUNDEEP
mysqlsql
Webshop2229
Webshop2229

Reputation: 27

Count or sum a field in mysql

I select a start and an end date, order status, then i list out the orders from my table.

enter image description here

The last column means, that how many orders were on that date. I want to get a number, that how many orders were at all. I mean: From 2017-01-01 to 2017-01-31 there was 127 order.

The result will be 49 order, if we use my picture.

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

Full code

$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>';
               }

Upvotes: 1

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Your query would look something like this:

SELECT datum, COUNT(rendeles_id) AS num_rows,
       SUM(num_orders) as num_orders
FROM rendeles_adatok 
WHERE datum >= '$date_start' AND (datum <= '$date_end' AND status = $rendeles_allapot)
GROUP BY datum
ORDER BY $kereses_sorrend;

Note: A count on orders per day would not usually be called _id (at least in English and other languages I'm familiar with). I don't know what your column is called, but you want the sum().

Upvotes: 1

Agustin Dortona
Agustin Dortona

Reputation: 111

You have to use SUM() to get the total sum of that column.

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() would only count the number of rows that match your search, while SUM() adds the values of the specified column.

Upvotes: 1

Related Questions