如何使用 codeIgniter 根据 id 对数据库中的数据求group_by - How to sum data from the database based on the id by using group_by codeIgniter

我试图将数据库中的数据汇总为从字段中获取的group_by

名称经销商 ID 经销商数量
  詹姆斯     001 5
  托马斯         002 7
  比利      005 4
  詹姆斯               001 6
  托马斯     002 9

我想要放出

詹姆斯   = 11
托马斯   =16
比利          = 4

这是我的控制器

$data=array('reseller'  =>$this->hasil_m->view_hasil(),
            'isi'       =>'admin/hasil'
        );
$this->load->view('dashboard/wrapper',$data);

我的模型

function view_hasil() {
    $this->db->select('*');
    $this->db->from('tb_produk as pro');
    $this->db->join('tb_reseller as res', 'pro.reseller_produk  = res.nomor_reseller');
    $this->db->join('tb_pelanggan as pel', 'pro.id_barcode  = pel.id_barcode');     
    $this->db->group_by('res.nomor_reseller');
    $ambil = $this->db->get('');
    if ($ambil->num_rows() > 0) {
        foreach ($ambil->result() as $data) {
            $hasil[] = $data;
        }
        return $hasil;
    }   
}

我的观点

<table class="table table-hover table-striped">
                <thead>
                <tr>    
                    <th><div align="center">No</th>
                     <th class="text-center">Nama Reseller</th>
                    <th><div align="center">ID Reseller</th>
                    <th><div align="center">Jumlah Produk Terjual</th>
                    <th><div align="center">Biaya ADM</th>                  
                    <th><div align="center">Total Di Terima</th>
                    <th><div align="center">Action</div></th>
                </tr>
                </thead>
                <tbody>
                <?php
                    if ($reseller == NULL) {
                    ?>
                    <div class="col-md-12">
                    <div class="alert alert-danger text-center" role="alert">Tanggal tidak ditemukan</div>
                    </div>
                    <?php
                    } else {
                    $no = 1;
                    $total_sum=0;
                    foreach ($reseller as $row) {
                    ?>
                    <tr>
                        <td><div align="center"><?php echo $no; ?></div></td>
                        <td class="fontCap"><?php echo $row->nama_reseller; ?></td>
                        <td><div align="center"><?php echo $row->nomor_reseller; ?></div></td>
                        <td class="fontCap text-center"><?php echo $total_sum+=$row;?></td>
                        <td class="fontCap text-center"></td>
                        <td><div align="center"></div></td>
                        <td><div align="center">
                            <a href="<?php echo site_url('admin/pelanggan/delete/' .$row->id_koperasi); ?>" title="delete" onclick="return confirm('Anda yakin ingin menghapus produk <?php echo $row->nama_produk; ?> dengan ID <?php echo $row->id_barcode; ?> pada database?');" class="btn btn-danger btn-xs"><i class="fa fa-trash-o"></i></a>
                            </div>
                        </td>
                            <?php
                            $no++;
                        }
                    }
                        ?>
                    </tr>
                </tbody>
            </table>

您需要先对其进行求和，然后按此字段分组Name Reseller ID reseller

   $this->db->select('nama_reseller,ID_Reseller,sum (Quantity) as qty');
   ///
   ///
   ///    
   $this->db->group_by(array('Name_Reseller','ID_reseller'));

或者，如果您愿意，可以->query()简单的方法

   $this->db->query('select pro.*,SUM(pro.Quantity) as Qty,res.*,pel.* from tb_produk as pro 
                    left join tb_reseller as res on pro.reseller_produk  = res.nomor_reseller
                    left join tb_pelanggan as pel on pro.id_barcode  = pel.id_barcode
                    group by Name_Reseller,ID_reseller');

尝试像这样选择：

$this->db->select('SUM(pro.jumlah) as totalQuantity, pro.*');

在选择中使用SUM(Quantity)。请参阅下面的代码

 function view_hasil() 
 {
    $this->db->select('SUM(pro.Quantity) as Qty,YOUR_OTHER_FIELDS');
    $this->db->from('tb_produk as pro');
    $this->db->join('tb_reseller as res', 'pro.reseller_produk  = res.nomor_reseller');
    $this->db->join('tb_pelanggan as pel', 'pro.id_barcode  = pel.id_barcode');
    $this->db->group_by(['res.nomor_reseller','pro.ID_reseller']);
    $ambil = $this->db->get();
    if ($ambil->num_rows() > 0) {
       //no need to looping it again $ambil->result() this is also an array
        return $ambil->result();
   }   
}