MySql count and group by question pretty tricky

[claudfs]

Hey Guys,

 

Ok here is my problem:

 

There is around 350 rows in the db.

 

All the variables $actor_id, $comment_id and $likes_id are all uid's for facebook users

 

Now i want to group and sum how many entries there are of each unique uid

 

Say for example the number:

 

'65653171' came up

10 times as a $actor_id

5 times as a $comment_id

3 times as a $likes_id

 

and

 

'67418530' came up

5 times as a $actor_id

21 times as a $comment_id

3 times as a $likes_id

 

Then i want a query that would display in order of most entries:

1.) 67418530 (29)

2.) 65653171 (18)

 

 

Thanks for the help, real brain teazer for me.

 

 

This is the code i use to fetch the Data from the FQL Table, a preview of the FQL Table can been seen on the bottom of this thread.

 

 $query = "SELECT post_id, actor_id, comments, likes FROM stream WHERE source_id = ".$user." LIMIT 500";
$result = mysql_query($query);
$count = count($result);
$total=$count;


for ($i = 0; $i < $count; $i++)
{

$actor_id = $result[$i][actor_id];
$count_c  = $result[$i][comments][count];

if($count_c > 0){
    for ($a = 0; $a < $count_c; $a++){
          $comment_id = $result[$i][comments][comment_list][$a][fromid];

    }
}

  
$count_l  = $result[$i][likes][count];

if($count_l > 0){        
        for ($b = 0; $b < $count_l; $b++){
            $likes_id = $result[$i][likes][friends][$b];
       
   }        
}




}

This is how the information is stored in the DB

 

[posts] => Array

        (

            [0] => Array

                (

                    [post_id] => 65653171_199696556137

                    [viewer_id] => 65653171

                    [source_id] => 65653171

                    [type] => 46

                    [app_id] => 291512034

                    [attribution] => via <a href="/mobile/?v=web">Mobile Web</a>

                    [actor_id] => 65653171

                    [target_id] => 

                    [message] => is just trying to be a nice guy  stop hating on me.

                    [attachment] => 

                    [app_data] => 

                    [action_links] => 

                    [comments] => Array

                        (

                            [can_remove] => 1

                            [can_post] => 1

                            [count] => 2

                            [comment_list] => Array

                                (

                                    [0] => Array

                                        (

                                            [fromid] => 67418530

                                            [time] => 125705944

                                            [text] => Y these ppl hating on yu bebe  miss yu sooo much!!

                                            [id] => 65653171_199696556137_6751049

                                        )



                                    [1] => Array

                                        (

                                            [fromid] => 108832181

                                            [time] => 1257186632

                                            [text] => yo

                                            [id] => 65653171_199696556137_6802381

                                        )



                                )



                        )



                    [likes] => Array

                        (

                            [href] => http://www.facebook.com/social_graph.php?node_id=19966556137&class=LikeManager

                            [count] => 4

                            [sample] => 

                            [friends] => Array

                                (

                                    [0] => 58579401

                                    [1] => 55125266

                                    [2] => 57449386

                                    [3] => 60906602

                                )



                            [user_likes] => 0

                            [can_like] => 1

                        )



                    [privacy] => Array

                        (

                            [value] => NOT_EVERYONE

                        )



                    [updated_time] => 1257186632

                    [created_time] => 1257057706

                    [tagged_ids] => 

                    [is_hidden] => 0

                    [filter_key] => 

                    [permalink] => http://www.facebook.com/profile.php?id=65653171&v=feed&story_fbid=19969655137

                )



            [1] => Array

                ( .........Next entry same format as top........

[lmhart]

I believe the query will look something like

select sum(actor_id,comment_id,likes_id) as total from table where uid = '.$uid';

[fenway]

SUM() only takes a single parameter... but you can add multiple SUM()s together.