Group similar items

Question

Here is my table structure with data

    id     actor_id   created_at              updated_at 

    729    80         2012-09-10 17:05:59    2012-09-10 17:05:59
    731    80         2012-09-10 17:04:02    2012-09-10 17:04:02
    725    139        2012-09-06 13:59:08    2012-09-06 13:59:08
    724    76         2012-09-06 11:31:30    2012-09-06 11:31:30
    723    29         2012-09-06 09:40:22    2012-09-06 09:40:22
    719    29         2012-09-06 09:24:02    2012-09-06 09:24:02
    811    80         2012-09-02 17:05:59    2012-09-10 17:05:59
    812    80         2012-09-01 17:04:02    2012-09-10 17:04:02

This is the result of

SELECT  `te`.`id`, te.actor_id, te.created_at, te.created_at
FROM `timeline_events` AS te
ORDER BY 
    te.created_at DESC
    LIMIT 10

I need group it by actor_id and created_at

Here is what i need in the end

    id    actor_id     created_at          updated_at             count

    729    80       2012-09-10 17:05:59    2012-09-10 17:05:59     2
    725    139      2012-09-06 13:59:08    2012-09-06 13:59:08     1
    724    76       2012-09-06 11:31:30    2012-09-06 11:31:30     1
    723    29       2012-09-06 09:40:22    2012-09-06 09:40:22     2
    812    80       2012-09-10 17:04:02    2012-09-10 17:04:02     2

Can someone guide me how to do this?

Many thanks in advance

UPD To simplify i will put another example

So say i have next rows

1                                             1 (count: 2)
1
3                                             3 (count: 1)
4
4     => after magic function it should be    4 (count: 2)
1
1                                             1 (count: 3)
1
6                                             6 (count: 2)
6
4                                             4 (count 3)
4
4

So it should split by groups.

UPD 2 I need this query for rendering timeline. Right now it show all info what user did, but i need group it.

Before

user1 upload photo
user1 changed information
user2 updated bio
user3 uploaded photo
user3 updated bio
user1 update bio

After

user1 uploadd photo and changed infomarion
user2 updated bio
user3 uploaded photo and updated bio
user1 updated bio

Good question, this stumps a lot of people in my experience. Here's a link to a similar query: http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns — Thomas Wright, Sep 17 '12 at 14:42
What aggregate function do you want to use with the field `id`? for example why do you want to get the ` 729 80 ` not `731 80 `?? — Mahmoud Gamal, Sep 17 '12 at 14:46
@MahmoudGamal id is not important is this query. This query for grouping similar events(made by one user) in timeline — timfjord, Sep 17 '12 at 15:20
@ThomasWright thank you for the link. But they try to order data but i need group it — timfjord, Sep 17 '12 at 16:33

score 2 · Answer 1 · answered Sep 17 '12 at 14:55

2

I think this might be what you are trying to do:

select t1.id,
  t1.actor_id,
  max(created_at) created_at,
  updated_at,
  t2.total
from yourtable t1
left join
(
  select count(*) total, date(created_at) dt, actor_id
  from yourtable
  group by actor_id, date(created_at)
) t2
  on t1.actor_id = t2.actor_id
  and date(t1.created_at) = t2.dt
group by  t1.actor_id, date(t1.created_at)
order by t1.created_at desc

see SQL Fiddle with demo

I am grouping by actor_id and the date using the DATE() function

answered Sep 17 '12 at 14:55

Taryn

242,637
56
362
405

Thanks, very similar. But why it didn't group last two columns(where actor_id is 80) – timfjord Sep 17 '12 at 15:12
Maybe it's my fault, because i didn't exaplain correct So it should not use created_at for group, it should sort by this field only, but group by actor_id – timfjord Sep 17 '12 at 15:14
@neimo0o I did `group by` the `created_at` field in both the subquery and the outer one. I just applied the `DATE()` function to it to return the date part only and strip the time – Taryn Sep 17 '12 at 15:15
you group by created_at field, but i do not need it. I need group by similar fields, smth [like this](http://stackoverflow.com/questions/1478070/how-to-group-consecutive-similar-items-of-a-collection) – timfjord Sep 17 '12 at 15:37
Maybe I am misunderstanding your requirements then. If you do not `group by created_at` then you will return the total `count` for the actor...so for your sample data `actor_id = 80` will have a `count` of 4, is that what you want? – Taryn Sep 17 '12 at 15:39
we should split results by group. When actor_id changed, mean that start another gruop – timfjord Sep 17 '12 at 15:41
see [this link](http://stackoverflow.com/questions/1478070/how-to-group-consecutive-similar-items-of-a-collection) – timfjord Sep 17 '12 at 15:42
i have added another example, maybe it will help – timfjord Sep 17 '12 at 16:09
@neimo0o since you found the answer, you should post it and then accept it. It will help future visitors. Glad you figured it out. I neve got the chance the go back and try to redo it. :) – Taryn Sep 19 '12 at 10:06
thanks for the tip. I am new in stackoverflow(in author role) – timfjord Sep 19 '12 at 12:16

score 1 · Accepted Answer · answered Sep 18 '12 at 09:26

I find solution by myself

Here is the query

SET @i = 0;
SELECT
    COUNT(`wrapper`.`id`) AS 'count',
    GROUP_CONCAT(`wrapper`.`type` SEPARATOR ',') as 'types'
FROM (
    SELECT  
        @prev := (
            SELECT prev_te.actor_id
            FROM `timeline_events` AS prev_te
            WHERE prev_te.created_at > te.created_at
            ORDER BY prev_te.created_at ASC
            LIMIT 1
        ) AS 'prev_actor_id',
        IF(
            @prev = te.`actor_id` OR @prev IS NULL, 
            @i, 
            @i := @i + 1
        ) AS 'actor_id_group',
        `te`.*
    FROM `timeline_events` AS te
    ORDER BY 
        te.created_at DESC, 
        te.id DESC
) AS `wrapper`
GROUP BY `wrapper`.`actor_id_group`
LIMIT 10

And here is the proof link ;-)

This website really helped me

I am using wrapper for grouping purpose, because mysql didn't group by variables, if someone know better solution, please let me know

Teena Thomas · Answer 3 · 2012-09-17T18:28:04.070

0

Add GROUP BY actor_id before order by clause.

Latest edit: select distinct actor_id, count(actor_id), created_at from actors group by actor_id order by created_at desc;

edited Sep 17 '12 at 18:28

answered Sep 17 '12 at 14:43

Teena Thomas

5,139
1
13
17

But What aggregate function to use with the field `id` and other fields that are not in the group by clause? – Mahmoud Gamal Sep 17 '12 at 14:47
@sweettea if i add GROUP BY it will group by all ids, but i need to split by groups (i have added anohter example) – timfjord Sep 17 '12 at 16:11
try ORDER BY te.created_at DESC, te.actor_id. This will order by created_at first, then by actor_id. – Teena Thomas Sep 17 '12 at 16:29
@sweettea but i need group them, not just order. – timfjord Sep 17 '12 at 16:31
try the latest edit from my answer above: `select distinct actor_id, count(actor_id), created_at from actors group by actor_id order by created_at desc;` – Teena Thomas Sep 17 '12 at 18:30
@sweettea thanks for help, but unfortunately this is not what i need. I have found [solution](http://stackoverflow.com/a/12474091/1078179) – timfjord Sep 18 '12 at 09:32

score 0 · Answer 4 · answered Sep 17 '12 at 14:43

0

SELECT te.id, te.actor_id, te.created_at, te.updated_at , COUNT(*) FROM timeline_events AS te GROUP BY te.actor_id, te.created_at ORDER BY te.created_at DESC LIMIT 10

answered Sep 17 '12 at 14:43

Shehzad Bilal

2,535
2
18
27

This is not correct because the field `te.id` is not in the group by clause nor in an aggregate function – Mahmoud Gamal Sep 17 '12 at 14:47
Yes, this is not what i need, because i do not need all ids group under one row – timfjord Sep 17 '12 at 15:09

Group similar items

4 Answers4

see SQL Fiddle with demo