CODEWITHSUNDEEP
phpmysqlcodeigniter
avinashse
avinashse

Reputation: 1460

query in sql codeigniter

I have a table tickets(username,posts,scheme) users may have may posts. for e.g.,

____________________________________
username  |    posts   |    scheme |
__________|____________|___________|
A         |    post1   |      10   |
__________|____________|___________|
B         |    post2   |      2    |
__________|____________|___________|
B         |    post 3  |      13   |
__________|____________|___________|
A         |    post 4  |      21   |
__________|____________|___________|
A         |    post 5  |      -1   |
__________|____________|___________|

My query should geneate output distinct username with total number of titles and total reputation. i.e.,

_______________________
|A   |   3   |   30    |
|____|_______|_________|
|B   |   2   |   15    |
|____|_______|_________|

My model function:-

function getAllUsers(){                      
        $this->db->distinct();
        $this->db->select('username,COUNT(title) AS numtitle');
        $this->db->where('site_referers_id',1);
        return $this->db->get('tbl_tickets');
     }

but that doesnt seem to work :(

Upvotes: 0

Views: 121

Answers (3)

Kumar Rakesh
Kumar Rakesh

Reputation: 2708

I know this question asking very ago, But old answers not get attention to author. Try with the SQL :

$sql ="SELECT count(*),username,SUM(scheme) FROM `tble_one` group by username";
$result = $this->db->query($sql)->get();

Upvotes: 0

Mischa
Mischa

Reputation: 43298

You should use $this->db->group_by() instead of distinct:

function getAllUsers(){                      
  $this->db->select('username, COUNT(*) AS numtitle, SUM(scheme) AS total');
  $this->db->where('site_referers_id',1);
  $this->db->group_by('username');
  return $this->db->get('tbl_tickets');
}

Upvotes: 4

naveen
naveen

Reputation: 1068

check this code

    $this->db->select('username,COUNT(title) AS numtitle');
    $this->db->where('site_referers_id',1);
     $this->db->group_by('username');
    return $this->db->get('tbl_tickets');

you get the perfect result

Upvotes: 2

Related Questions