Mixing
count in group_concat in mysql in one query
1
I have one to many table relationship :
- one
userfor multipleevent
- one
eventfor multipleevent_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
user3006967user3006967
78832039
add a comment |
1
I have one to many table relationship :
- one
userfor multipleevent
- one
eventfor multipleevent_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
user3006967user3006967
78832039
add a comment |
1
1
1
I have one to many table relationship :
- one
userfor multipleevent
- one
eventfor multipleevent_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
user3006967user3006967
78832039
I have one to many table relationship :
- one
userfor multipleevent
- one
eventfor multipleevent_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
mysql
asked Nov 22 '18 at 2:46
user3006967user3006967
78832039
asked Nov 22 '18 at 2:46
user3006967user3006967
78832039
asked Nov 22 '18 at 2:46
user3006967user3006967
78832039
asked Nov 22 '18 at 2:46
user3006967user3006967
78832039
asked Nov 22 '18 at 2:46
user3006967user3006967
78832039
78832039
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
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
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim 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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim 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 |
1
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
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim 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 |
1
1
1
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
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim 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
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim Biegeleisen
228k1394147
edited Nov 22 '18 at 3:20
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim Biegeleisen
228k1394147
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim Biegeleisen
228k1394147
answered Nov 22 '18 at 3:05
Tim BiegeleisenTim Biegeleisen
228k1394147
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, 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, 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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
