Tukukkk

I have one to many table relationship :

• one user for multiple event
  


• one event for multiple event_attribute

Now, I group by userId and want to know how many for each event attribute ?

I am using group_concat like this:

group_concat(  

concat(event_event_attribute.event_attr_id, 

       count( distinct event_event_attribute.value) 

      ) group by event_attr_id)

)

group by userId

So here, I first group by userId, then group concat event-attribute, at least I hope to have :

(attr1, 10),(attr2, 30)....

all in one row.

But this does not work at all

Any suggestions?

To be more specific, this is the DB schema I am using:

CREATE TABLE `user` (

  `id` int(11) NOT NULL,

  `name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `id_UNIQUE` (`id`)

);

CREATE TABLE `event` (

  `id` int(11) NOT NULL,

  `name` varchar(45) DEFAULT NULL,

  `user_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

);

CREATE TABLE `event_attr` (

  `id` int(11) NOT NULL,

  `att_name` varchar(45) DEFAULT NULL,

  `event_id` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`)

);

INSERT INTO `user` VALUES (1,'user1'),(2,'user2'),(3,'user3');

INSERT INTO `event` VALUES (1,'event1',1),(2,'event2',1),(3,'event3',1),(4,'event4',2),(5,'event5',2),(6,'event6',3);

INSERT INTO `event_attr` VALUES (1,'att1','1'),(2,'att2','1'),(3,'att3','1'),(4,'att1','2'),(5,'att2',NULL);

Now if I am running:

select u.id, group_concat(e.name)

from user u 

join event e on u.id=e.user_id 

group by u.id

I will get:

1 event1,event2,event3

2 event4,event6

3 event 6

That is fine. But one step forward, I need to know count for each event_attt for each user, such as:

1 evet_att1:3;event_att2:2

2 event_att3:1

Then it is not possible. Can I use just one query to get above expected response?

should be the inverse alias concat the aggreagted values and not aggregated the concat

 select concat (group_concat(event_event_attribute.event_attr_id )

          ,' - ',  

         count( distinct event_event_attribute.value) )

 from event_event_attribute

 group by userid

Otherwise could be you need an subquery for obtain the count group by event_attr_id

select group_concat(

    concat(event_attr_id), ',', count_value)

)

from t (

    select user_id, event_event_attribute.event_attr_id, count( distinct event_event_attribute.value)  count_value

    from event_event_attribute

    group by event_attr_id

) t 

group by user_id