count in group_concat in mysql in one query

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 ?

Below is the data schema you can use:

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?

asked Nov 22 '18 at 2:46

user3006967

78832039

add a comment |

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 ?

Below is the data schema you can use:

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?

asked Nov 22 '18 at 2:46

user3006967

78832039

add a comment |

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 ?

Below is the data schema you can use:

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?

asked Nov 22 '18 at 2:46

user3006967

78832039

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 ?

Below is the data schema you can use:

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?

mysql

asked Nov 22 '18 at 2:46

user3006967

78832039

asked Nov 22 '18 at 2:46

user3006967

78832039

asked Nov 22 '18 at 2:46

user3006967

78832039

asked Nov 22 '18 at 2:46

user3006967

78832039

asked Nov 22 '18 at 2:46

user3006967

78832039

add a comment |

1 Answer
1

active

oldest

votes

We can try aggregating the event table first by both user and event, to generate the counts:

SELECT u.id, GROUP_CONCAT(e.name, ':', CAST(e.cnt AS CHAR(50)))

FROM user u 

LEFT JOIN

(

    SELECT id, name, user_id, COUNT(*) AS cnt

    FROM event

    GROUP BY id, name, user_id

) e

    ON u.id = e.user_id

GROUP BY

    u.id;

Demo

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423174%2fcount-in-group-concat-in-mysql-in-one-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

We can try aggregating the event table first by both user and event, to generate the counts:

SELECT u.id, GROUP_CONCAT(e.name, ':', CAST(e.cnt AS CHAR(50)))

FROM user u 

LEFT JOIN

(

    SELECT id, name, user_id, COUNT(*) AS cnt

    FROM event

    GROUP BY id, name, user_id

) e

    ON u.id = e.user_id

GROUP BY

    u.id;

Demo

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

add a comment |

We can try aggregating the event table first by both user and event, to generate the counts:

SELECT u.id, GROUP_CONCAT(e.name, ':', CAST(e.cnt AS CHAR(50)))

FROM user u 

LEFT JOIN

(

    SELECT id, name, user_id, COUNT(*) AS cnt

    FROM event

    GROUP BY id, name, user_id

) e

    ON u.id = e.user_id

GROUP BY

    u.id;

Demo

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

add a comment |

We can try aggregating the event table first by both user and event, to generate the counts:

SELECT u.id, GROUP_CONCAT(e.name, ':', CAST(e.cnt AS CHAR(50)))

FROM user u 

LEFT JOIN

(

    SELECT id, name, user_id, COUNT(*) AS cnt

    FROM event

    GROUP BY id, name, user_id

) e

    ON u.id = e.user_id

GROUP BY

    u.id;

Demo

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

We can try aggregating the event table first by both user and event, to generate the counts:

SELECT u.id, GROUP_CONCAT(e.name, ':', CAST(e.cnt AS CHAR(50)))

FROM user u 

LEFT JOIN

(

    SELECT id, name, user_id, COUNT(*) AS cnt

    FROM event

    GROUP BY id, name, user_id

) e

    ON u.id = e.user_id

GROUP BY

    u.id;

Demo

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

edited Nov 22 '18 at 3:20

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

answered Nov 22 '18 at 3:05

Tim Biegeleisen

228k1394147

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

add a comment |

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

Thanks, but we want to count event_attribute, not event, you are counting event here, any idea?

– user3006967
Nov 22 '18 at 23:55

Thanks, very good solution. if we need to count attribute, we just need to group by it first, coming up with subQuery and then join again

– user3006967
Nov 23 '18 at 0:32

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu