COUNT GROUP BY with active record

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 the active record query method  Can this be done with COUNT and GROUP BY user id

User · Accepted Answer

I believe you ll want something like this     this- gt db- gt select  user id  COUNT user id  as total      this- gt db- gt group by  user id       this- gt db- gt order by  total    desc       this- gt db- gt 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

User · Answer

this- gt db- gt select  overal points     this- gt db- gt where  point publish   1    this- gt db- gt order by  overal points    desc      query    this- gt db- gt get  company   4 - gt result

User · Answer

Although it is a late answer  I would say this will help you      query    this- gt db               - gt select  user id  count user id  AS num of time                 - gt group by  user id                 - gt order by  num of time    desc                 - gt get  tablename   10   print r  query- gt result

User · Answer

This code counts rows with date range   Controller    this- gt load- gt model  YourModelName     data   query      this- gt YourModelName- gt get report      Model      public function get report                     query    this- gt db- gt query  SELECT    FROM   reservation WHERE arvdate  lt    2016-7-20  AND  dptrdate  gt    2016-10-25             return  query           where  arvdate  and  dptrdate   are two dates on database and  reservation  is the table name   View    lt  php  echo  query- gt num rows      gt    This code is to return number of rows   To return table data  then use    query- gt rows    return  row- gt table column name

User · Answer

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

[codeigniter] COUNT / GROUP BY with active record?

Examples related to codeigniter

Examples related to activerecord

Examples related to count

Examples related to group-by