Ahmad Ahmad - 16 days ago 8
MySQL Question

mysql group by with repeated value and count

i have a query with group by but i want to repeat values with count , for example , my result like this :

my query is :

SELECT
s.id,s.user_id,s.start_date,s.end_date,count(s.user_id) as num_of_subscriptions
FROM
subscriptions s join users u on (s.user_id= u.id )
GROUP BY s.user_id;

id user_id start_date end_date number_of_subscription

1 2 2016-05-20 2016-05-21 4

2 5 2016-05-20 2016-05-21 3

i want to get data like this

id user_id start_date end_date number_of_subscription

1 2 2016-05-20 2016-05-21 4

1 2 2016-05-10 2016-05-21 4

1 2 2016-05-11 2016-05-21 4

1 2 2016-05-12 2016-05-21 4

2 5 2016-05-20 2016-05-21 3

2 5 2016-05-20 2016-05-21 3

2 5 2016-05-20 2016-05-21 3

Answer

I think you need a sub query rather than a group by so given

drop table if exists t;
create table t
(user_id int,start_date date,end_date date);
insert into t values
( 2 ,'2016-05-20' ,'2016-05-21'),
( 2 ,'2016-05-10' ,'2016-05-21'),
( 2 ,'2016-05-11' ,'2016-05-21'),
( 2 ,'2016-05-12' ,'2016-05-21'),
( 5 ,'2016-05-20' ,'2016-05-21'),
( 5 ,'2016-05-20' ,'2016-05-21'),
( 5 ,'2016-05-20' ,'2016-05-21');

This

select user_id,start_date,end_date, (select count(*) from t t1 where t1.user_id = t.user_id)
from t t

Results in

+---------+------------+------------+----------------------------------------------------------+
| user_id | start_date | end_date   | (select count(*) from t t1 where t1.user_id = t.user_id) |

    +---------+------------+------------+----------------------------------------------------------+
    |       2 | 2016-05-20 | 2016-05-21 |                                                        4 |
    |       2 | 2016-05-10 | 2016-05-21 |                                                        4 |
    |       2 | 2016-05-11 | 2016-05-21 |                                                        4 |
    |       2 | 2016-05-12 | 2016-05-21 |                                                        4 |
    |       5 | 2016-05-20 | 2016-05-21 |                                                        3 |
    |       5 | 2016-05-20 | 2016-05-21 |                                                        3 |
    |       5 | 2016-05-20 | 2016-05-21 |                                                        3 |
    +---------+------------+------------+----------------------------------------------------------+
    7 rows in set (0.00 sec)
Comments