Mysql – Count of range on datetime in MySql

gaps-and-islandsMySQL

I want to group table entries for each userId only if they are consecutive entries in the table.
I have a table like this.

id userId      transactionTime

1  1           2016-04-13 22:15:44
2  1           2016-04-13 22:40:22
3  1           2016-04-13 22:41:34
4  2           2016-04-13 22:41:34
5  2           2016-04-13 22:42:25
6  1           2016-04-13 23:43:55

Expected Result:

userId    count
1         3
2         2
1         1

Thanks 🙂

Update:
The transactionTime is ordered in asc. Considering this, we can check the consecutive occurence of same userId and make it a single entry with number of counts.

Consider this, userId=1 occurs three consecutive times from id=1,2,3 this will be first result with userId=1 and count=3. Similarly at id=4 and 5 userId is 2 which will be second row in the result with userId=1 and count=2.

Best Answer

You can use variables for this kind of thing:

select userId, count(*)
from (
    select @g:=case when userId=@lastuserId then @g else @g+1 end AS grp,
           @lastuserId:=userId AS userId
    from test1, (select@lastuserId:=0, @g:=0) vars
    order by transactionTime
) groups
group by grp, userId
order by grp

Related Solutions

Mysql – way to have MySQL GROUP BY utilize trailing whitespace

char values are space-padded when they are stored, so all trailing whitespace is assumed to be padding and removed when retrieving values. varchar fields will retain trailing whitespace, but by default it is removed for comparisons. (see The CHAR and VARCHAR types)

To retain the trailing whitespace for varchar values, use the binary operator. Something like SELECT foo, CHAR_LENGTH(foo) FROM myTable WHERE foo='bar' GROUP BY BINARY foo should do what you want.

MySQL Group By – Fetch Data Using Two Group By Clauses

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    DATE_FORMAT(dt,'%M')       "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    DATE_FORMAT(dt,'%M') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

If you would like the counts summed up, include WITH ROLLUP

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;

Best Answer

Related Solutions

Mysql – way to have MySQL GROUP BY utilize trailing whitespace

MySQL Group By – Fetch Data Using Two Group By Clauses

Related Question