How to COUNT() aggregate rows when using GROUP BY with CodeIgniter's query builder

Question

I have a table with the following info:

id  |  user_id  |  points
--------------------------
1   |  12       |  48
2   |  15       |  36
3   |  18       |  22
4   |  12       |  28
5   |  15       |  59
6   |  12       |  31

etc.

What I want is a top 10 (array) with most entries per user_id (order high to low). So using the table above I need the following array in return:

12 => 3 rows
15 => 2 rows
18 => 1 row
etc.

How can I do this with CodeIgniter using query builder methods? Can this be done with COUNT and GROUP BY user_id?

Brett DeWoody · Accepted Answer · 2018-10-02 13:42:27Z

82

I believe you'll want something like this:

 $this->db->select('user_id, COUNT(user_id) as total');
 $this->db->group_by('user_id'); 
 $this->db->order_by('total', 'desc'); 
 $this->db->get('tablename', 10);

This will produce a result like

|  USER_ID |  TOTAL  |
|    12    |    3    |
|    15    |    2    |
|    18    |    1    |

UPDATE: As some pointed out in the comments the original query was summing the user_ids rather than counting them. I've updated the active record query to correct this.

edited Oct 2, 2018 at 13:42

answered Jan 25, 2012 at 7:31

Brett DeWoody

63.3k31 gold badges145 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ruben Müller Over a year ago

You could also use the function $this->db->last_query() to see what active record generated from the functions User Guide: Query Helper Functions

Brett DeWoody Over a year ago

That works too, though you often need to change a bunch of CI settings so it doesn't throw an error when it tries to run an invalid query.

bukowski Over a year ago

I'd like to point out that it counts the ids not the rows.. thats why vote down.

Brett DeWoody Over a year ago

I've updated my answer to COUNT rather than SUM the user_ids.

Mancy Over a year ago

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement. the first line should be $this->db->select('user_id, COUNT(user_id) as total', FALSE);

richplane · Accepted Answer · 2014-01-08 11:53:38Z

9

Although it is a late answer, I would say this will help you...

$query = $this->db
              ->select('user_id, count(user_id) AS num_of_time')
              ->group_by('user_id')
              ->order_by('num_of_time', 'desc')
              ->get('tablename', 10);
print_r($query->result());

edited Jan 8, 2014 at 11:53

richplane

5474 silver badges13 bronze badges

answered Nov 13, 2013 at 7:43

NULL

1,8481 gold badge21 silver badges23 bronze badges

Comments

uzsolt · Accepted Answer · 2012-01-24 16:36:21Z

2

I think you should count the results with FOUND_ROWS() and SQL_CALC_FOUND_ROWS. You'll need two queries: select, group_by, etc. You'll add a plus select: SQL_CALC_FOUND_ROWS user_id. After this query run a query: SELECT FOUND_ROWS(). This will return the desired number.

answered Jan 24, 2012 at 16:36

uzsolt

6,0872 gold badges24 silver badges35 bronze badges

Comments

Aslam Kakkove · Accepted Answer · 2016-10-06 02:07:59Z

1

This code counts rows with date range:

Controller:

$this->load->model("YourModelName");
$data ['query'] = $this->YourModelName->get_report();

Model:

  public function get_report()
     {   
       $query = $this->db->query("SELECT  *
FROM   reservation WHERE arvdate <= '2016-7-20' AND  dptrdate >= '2016-10-25' ");
       return $query;
     }

where 'arvdate' and 'dptrdate' are two dates on database and 'reservation' is the table name.

View:

<?php
 echo $query->num_rows();
?>

This code is to return number of rows. To return table data, then use

$query->rows();
return $row->table_column_name;

answered Oct 6, 2016 at 2:07

Aslam Kakkove

971 silver badge12 bronze badges

Collectives™ on Stack Overflow

How to COUNT() aggregate rows when using GROUP BY with CodeIgniter's query builder

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related