how to count items inside group_concat method with mysql 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 ?
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?
mysql
asked Nov 20 '18 at 8:18
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 ?
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?
mysql
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
1
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59
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 ?
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?
mysql
asked Nov 20 '18 at 8:18
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 ?
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?
mysql
mysql
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
edited Nov 21 '18 at 16:59
user3006967
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
asked Nov 20 '18 at 8:18
user3006967user3006967
78832039
78832039
1
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59
add a comment |
1
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59
1
1
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59
add a comment |
1 Answer
1
active
oldest
votes
0
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
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
|
show 1 more 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%2f53388787%2fhow-to-count-items-inside-group-concat-method-with-mysql-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
0
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
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
|
show 1 more comment
0
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
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
|
show 1 more comment
0
0
0
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
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
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
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
edited Nov 20 '18 at 17:56
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
answered Nov 20 '18 at 8:22
scaisEdgescaisEdge
94.8k104970
94.8k104970
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
|
show 1 more comment
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
Hello, the problem in your solution is you are not grouping by event_attr_id, one user has many events, we need to could each event attribute's count instead of all the attribute as a whole
– user3006967
Nov 20 '18 at 16:29
1
1
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
add a proper data sample the expected result ..
– scaisEdge
Nov 20 '18 at 17:51
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
anyway answer updated with a suggestion
– scaisEdge
Nov 20 '18 at 17:56
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
I updated my post, please see above detail, thanks
– user3006967
Nov 21 '18 at 17:01
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
your expected result is not coherent with e data in your sample eg: att1 is related only tu user 1 and 2 so hwo you expected thre result evet_att1:3; ????
– scaisEdge
Nov 21 '18 at 17:34
|
show 1 more 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%2f53388787%2fhow-to-count-items-inside-group-concat-method-with-mysql-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
1
the thing you posted is not a query. post your query, post the results you get, post the results you want. bonus: post your table schema. double bonus: post sample data. triple bonues!: post a sql fiddle!
– billynoah
Nov 20 '18 at 17:59