Select, count and where using codeigniter and mysql

You can use this query for this

SELECT
  tblSaler.SalerName,
  count(tblProduct.ProductID) as Total
FROM tblSaler
  LEFT JOIN tblProduct
    ON tblProduct.SalerID = tblSaler.SalerID
GROUP BY tblSaler.SalerID

And here is the active record for this

$select =   array(
                'tblSaler.SalerName',
                'count(tblProduct.ProductID) as Total'
            );  
$this->db
        ->select($select)
        ->from('tblSaler')
        ->join('tblProduct','Product.SalerID = tblSaler.SalerID','left')
        ->group_by('tblSaler.SalerID')
        ->get()
        ->result_array();

Demo

OUTPUT

_____________________
| SALERNAME | TOTAL |
|-----------|-------|
|   sothorn |     1 |
|      Daly |     2 |
|    Lyhong |     3 |
|   Chantra |     1 |
_____________________           

Please try this code. Its working fine for me and it will help you also.

$this->db->select('SalerName, count(*)');
$this->db->from('tblSaler');        
$this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID'); 
$this->db->group_by('tblSaler.SalerID');       
$query = $this->db->get();

You can get whole SQL query using this line below

$query = $this->db->get(); 
echo $this->db->last_query();

Try This

$this->db->select('SalerName, count(*)'); 
$this->db->from('tblSaler'); 
$this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID');
$this->db->group('SalerID');