CODEWITHSUNDEEP
mysqlsqlgroup-bygrouping
timfjord
timfjord

Reputation: 1829

Group similar items

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

After

Upvotes: 0

Views: 352

Answers (4)

timfjord
timfjord

Reputation: 1829

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

Upvotes: 1

Teena Thomas
Teena Thomas

Reputation: 5239

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;

Upvotes: 0

Taryn
Taryn

Reputation: 247690

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

Upvotes: 2

Shehzad Bilal
Shehzad Bilal

Reputation: 2523

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

Upvotes: 0

Related Questions