MYSQL get COUNT of highest column value for different phases

This is a pretty complicated database. So I have simplified the structure for easy understanding.

I have 2 tables TABLE A : transactions

p_id     rev_count 1        1 2        1 3        1 1        1 1        1 1        2 (Gets incremented as it has reached max entries) 2        1 2        1 2        2 (Gets incremented as it has reached max entries) 1        2 2        2 

TABLE B : rev_countReference

p_id     rev_count 1        2 2        2 3        1 

So basically Table B stores the rev_count value as reference for TABLE A. Since p_id can accept only 3 transactions, whenever p_id goes exceeds 3 count the rev_count value is incremented and next p_id entry will have an incremented rev_count value.

QUESTION I want to get number of transactions occupied in the maximum rev_count by each p_id.

Desired Output :

p_id      used_rev 1         1 2         2 3         1 

Tried so far :

Unfortunately i’m not able to write a code for the above DB example yet. But what I have tried so far with actual code.

 public function getfreeslots($user_id) {                  $this->db->select('count(t.id), t.reinvest_count, MAX(t.reinvest_count) AS max_reinvest, COUNT(t.id) AS outstandingSlots');          $this->db->from('transactions t');          $this->db->join('reinvestCount rc', "t.receiver_id=rc.user_id AND t.phase_id=rc.phase_id",'LEFT');          $this->db->where('t.receiver_id', $user_id);                  $this->db->having('t.reinvest_count >= max_reinvest');         $this->db->group_by('t.phase_id');         $this->db->order_by('t.phase_id', 'ASC');                  $query = $this->db->get();          $row = $query->result();                  if (empty($row))             return FALSE;         return $row;              }           

I’m using Codeigniter query builder. So answer using query builder will be much appreciated.

Asked on July 16, 2020 in Mysql.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.