shank shank - 2 months ago 10
MySQL Question

counting number of occurances of a number in mysql -codeigniter

How to find the number of occurrences of

open_id_fk
in this table. I am using the below code in my model.

$this->db->select('open_id_fk, COUNT(open_id_fk) as total');
$this->db->group_by('open_id_fk');
$this->db->order_by('total', 'desc');
$query = $this->db->get('voting_ip', 10);
return $query;


enter image description here


Expected Output:

116 - 2 occurances 118 - 1 occurances 119 - 1 occurances

Acutual Output recieved:

116- 6 occurances 118- 3 occurances 119- 2 occurances

Answer

Just change COUNT(open_id_fk) to COUNT(*) in your query

 $this->db->select('open_id_fk, COUNT(*) as total');
 $this->db->group_by('open_id_fk'); 
 $this->db->order_by('total', 'desc'); 
 $query = $this->db->get('voting_ip', 10);