CODEWITHSUNDEEP
phpmysqlcodeigniter-3
Duwi irwanto
Duwi irwanto

Reputation: 139

How to sum data from the database based on the id by using group_by codeIgniter

I tried to sum data from the database are taken from the field by group_by

Name Reseller     ID reseller       Quantity
  James                   001                   5
  Thomas                 002                   7
  Billy                        005                   4
  James                   001                   6
  Thomas                 002                  9

I want Out put

James     = 11
Thomas   =16
Billy          = 4

This my controllers

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

My models

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

My Views

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

Upvotes: 2

Views: 1020

Answers (3)

Vinie
Vinie

Reputation: 2993

Use SUM(Quantity) in your select. See below code

 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();
   }   
}

Upvotes: 0

Boby
Boby

Reputation: 1202

You need to sum it first, then group by this fields Name Reseller ID reseller

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

Or if you want you can ->query() for simple way

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

Upvotes: 2

itzmukeshy7
itzmukeshy7

Reputation: 2677

Try select like this:

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

Upvotes: 0

Related Questions