I need help with this SQL query

[eluser]Tim Brownlaw[/eluser]
I just tried something similar and got this...

You can add in extra Select Statements and for your count - it's best to use a primary index if you have one, Try to avoid using * and be specific.

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->select('count(id) as count');  // New Bit
        $this->db->from('users');
        $this->db->where('active', '1');
        $this->db->group_by('residency);          // New Bit
        $query = $this->db->get();
        return $query->result();
}

Also you can loose the "From" statement and put the table name in the get

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->select('count(id) as count');      // New Bit
        $this->db->where('active', '1');
        $this->db->group_by('residency);
        $query = $this->db->get('users');             // Changed
        return $query->result();
}

Those are just some of the ways you can achieve what you want.