Codeigniter ActiveRecord, count records by category

Question

Maybe I just can't think right currently. But what I want to try and figure out is how can I return a set of results that is the count of rows per category, in the DB. Without having to query based on each category.

The categories in this case are states. I have 90,000 rows of data with 50 different states dividing the data up. Is there a way of creating a count that is associated with each state?

I want to say group_by or something but I just honestly can't think of it right now. Ideas?

member | comment | state | dataset
----------------------------------
000    |something| CA    | an_object_of_data
000    |something| CA    | an_object_of_data
000    |something| CA    | an_object_of_data
000    |something| NY    | an_object_of_data
000    |something| NY    | an_object_of_data
000    |something| CA    | an_object_of_data
000    |something| CT    | an_object_of_data
000    |something| CA    | an_object_of_data

the above is an example of the db table in sorts. Imagine something like that with 90k rows and more state.

If I can some how get a count returned by grouping the results or something, ie: "CA=4", "CT=1", "NY=2"

yes you can. But it would be nice to know more about your db. show us a create table. — steven
– steven, Commented Jul 16, 2013 at 6:58

deviloper · Accepted Answer · 2015-01-23 17:36:27Z

10

select state,  count(state) from states group by state order by state ASC;

I guess this might work in its simplest way!

CI Style:

$this->db->select("state, count(state)");
$this->db->from("states");
$this->db->group_by("state");
$this->db->order_by("state", "ASC");
$this->db->get();

edited Jan 23, 2015 at 17:36

answered Jul 16, 2013 at 6:59

deviloper

7,25210 gold badges52 silver badges79 bronze badges

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

3 Comments

steven Over a year ago

group_by? are you shure?

chris Over a year ago

that actually works, exactly as I could have hoped! you have no idea how helpful that is, soo tired, crunching in last bits of stuff for a project. Just have to convert that to a CI active record variant of that query and Ill be good, I think I should be able to do that though using something like $this->db->query("select count(state),state from states group by state order by state ASC");

Mayank K. Swami Over a year ago

+1 and its good to see that there are also developers in Afghanistan :)

Amr · Accepted Answer · 2013-12-28 06:10:25Z

3

The code is

$this->db->select('count(states.state) as count,state',false)
         ->from(states)
         ->group_by('states.state')
         ->get()
         ->result();

edited Dec 28, 2013 at 6:10

Amr

5,1797 gold badges52 silver badges68 bronze badges

answered Jul 16, 2013 at 7:55

sanjeeviraj

1551 gold badge2 silver badges11 bronze badges

1 Comment

sanjeeviraj Over a year ago

@BobbyDigital i am new to stack overflow,will follow the rules surely.

Collectives™ on Stack Overflow

Codeigniter ActiveRecord, count records by category

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related