php - 如何使用 group_by codeIgniter 根据 id 从数据库中汇总数据

Question

我试图通过 group_by 从字段中获取数据库中的数据求和

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

我要输出

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

这是我的 Controller

$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>

Answer 1

您需要先求和，然后按此字段分组 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');