Correct way to select distinct from mysql

[eluser]Lpeek[/eluser]
Hey! I'm trying to select data from a table, where the uid, ip, and user_agent are distinct. Yet regardless of what I try I can only seem to count the total occurrences of uid.

My table has 30 entries with the same uid, of those, only 3 have a different ip and user_agent. So I'm trying to return 3, yet I always get 30.

This is my latest attempt:

Code:

$this->db->distinct();
$this->db->select(array('uid', 'ip', 'user_agent'));
$this->db->select_sum('uid', 'views');
$this->db->where('datetime >',$dbDateTime);
$this->db->group_by('uid');
$query = $this->db->get('poster_views');

I've also tried grouping by the three fields, but this returns multiple rows of the same uid, so instead of 1 row with 'views' as 3, I get 3 rows each with views such as 10, 15 and 5.

Code:

$this->db->group_by(array('uid','ip','user_agent'));

I've also tried including more field names in the the select_sum as arrays but this returns an invalid sql statement.

Any help is appreciated

[eluser]Aken[/eluser]
You've lost me. Why are you trying to select the UID, as well as the total sum of UIDs? That's not going to work.

Depending on what you're trying to do, a subquery might be required. Active record might be hindering you in this case.

[eluser]Lpeek[/eluser]
Hey Aken, I'm counting the number of times a single UID appears so I get the total 'views' of an item. Which I will then order by views DESC to show popularity of which items have been viewed the most. But then I still need to select the uid to be able to fetch the items full info from the database for displaying in the view.

So if this is my table:

Code:

| uid |   ip   | user_agent |
|  1  | 123... |   agent1   |
|  1  | 234... |   agent1   |
|  1  | 567... |   agent1   |
|  1  | 567... |   agent1   | //(this row wont count again, becuase the ip and user_agent match another row
|  2  | 123... |   agent1   |
|  3  | 123... |   agent1   |
|  3  | 234... |   agent1   |

My target is an array like this:

Code:

| uid | views |
|  1  |   3   |
|  3  |   2   |
|  2  |   1   |

[eluser]Aken[/eluser]
If you only want those two items returned by your query, that's all you should focus on. Adding the IP combined with DISTINCT is what's causing your row to not repeat.

To get your target array, all you'd need to do is something like:

Code:

$this-db
  ->select('uid, COUNT(*) AS views', false)
  ->group_by('uid')
  ->get('poster_views');

(untested, tweak as necessary)

[eluser]Lpeek[/eluser]
Thanks that makes sense not to select the ip or useragent, but that will still return all 4 rows with uid as 1. I can't figure how to remove the duplicates rows based on the uid, ip and useragent?