help after count() data - Printable Version

help after count() data - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: help after count() data (/showthread.php?tid=18565)

help after count() data - El Forum - 05-11-2009

[eluser]newbie boy[/eluser]
hi guys...

i need help, a function or so...

i can get the total numbers of a field on a certain table...

my problem is i need to show on top the one with highest total down to the lowest...

thanks guys...

help after count() data - El Forum - 05-11-2009

[eluser]Thorpe Obazee[/eluser]
you'll probably need to use order_by();

Code:
$this->db->order_by('field', 'DESC');

help after count() data - El Forum - 05-11-2009

[eluser]newbie boy[/eluser]
it's like this

field user group
1 1
2 1
3 1
4 2
5 3
6 3

i can count the total user from a certain group...

but i need to show on top the group with the highest total of user down to the lowest.

help after count() data - El Forum - 05-11-2009

[eluser]Thorpe Obazee[/eluser]
[quote author="newbie boy" date="1242048984"]
but i need to show on top the group with the highest total of user down to the lowest.[/quote]

Do you need to sort the group according to the number of members, descending?

If yes, you'll still be using the order_by and probably use sub queries.

help after count() data - El Forum - 05-11-2009

[eluser]Michael Wales[/eluser]

Code:
SELECT users_groups.COUNT(`user`) AS total, groups.title FROM users_groups LEFT JOIN groups ON (groups.id = users_groups.group) GROUP BY `group` ORDER BY total DESC

Code:
$this->db->select('COUNT(`user`) AS total', FALSE);

$this->db->join('groups', 'groups.id = users_groups.group');

$this->db->group_by('group');

$this->db->order_by('total DESC');

$this->db->get('users_groups');

Off the top of my head I can't guarantee that the ORDER BY will work (since total is just an alias) and I don't feel like opening Query Browser to test it. This will give you a good starting point though.