MySql group by with having clause The Next CEO of Stack OverflowShould I use the datetime or timestamp data type in MySQL?MySQL Query GROUP BY day / month / yearGroup By Multiple ColumnsHow to get a list of MySQL user accountsRetrieving the last record in each group - MySQLUsing group by on multiple columnsSelect first row in each GROUP BY group?Group by in LINQQuery to only retrieve records that are not duplicatedHow to import an SQL file using the command line in MySQL?
Which one is the true statement?
Reference request: Grassmannian and Plucker coordinates in type B, C, D
Scary film where a woman has vaginal teeth
Calculate the Mean mean of two numbers
Does higher Oxidation/ reduction potential translate to higher energy storage in battery?
How to get the last not-null value in an ordered column of a huge table?
Is it okay to majorly distort historical facts while writing a fiction story?
Is it ever safe to open a suspicious HTML file (e.g. email attachment)?
What flight has the highest ratio of timezone difference to flight time?
"Eavesdropping" vs "Listen in on"
What was Carter Burke's job for "the company" in Aliens?
Help/tips for a first time writer?
Could a dragon use its wings to swim?
Is it correct to say moon starry nights?
Film where the government was corrupt with aliens, people sent to kill aliens are given rigged visors not showing the right aliens
Is dried pee considered dirt?
The Ultimate Number Sequence Puzzle
How to avoid supervisors with prejudiced views?
What would be the main consequences for a country leaving the WTO?
Aggressive Under-Indexing and no data for missing index
What is the difference between "hamstring tendon" and "common hamstring tendon"?
Reshaping json / reparing json inside shell script (remove trailing comma)
Is it professional to write unrelated content in an almost-empty email?
what's the use of '% to gdp' type of variables?
MySql group by with having clause
The Next CEO of Stack OverflowShould I use the datetime or timestamp data type in MySQL?MySQL Query GROUP BY day / month / yearGroup By Multiple ColumnsHow to get a list of MySQL user accountsRetrieving the last record in each group - MySQLUsing group by on multiple columnsSelect first row in each GROUP BY group?Group by in LINQQuery to only retrieve records that are not duplicatedHow to import an SQL file using the command line in MySQL?
I have a table message with following structure
id - to_number - from_number - message - send_to_podio_time
100 - +16235708320 - +16232992010 - test message1 - 2019-03-07 18:31:12
101 - +16232992010 - +16235708320 - test message2 - 2019-03-07 18:31:15
102 - +16235708786 - +16232992010 - test message3 - 2019-03-07 18:31:16
103 - +16232992010 - +16235708786 - test message4 - 2019-03-07 18:31:18
I need to get the count of conversation thread between two phone numbers
The expected output is
phone_numbers - count
+16235708320:+16232992010 - 1
+16235708786:+16232992010 - 1
Can anybody help me with the appropriate query ?
mysql group-by having sql-query-store
edited Mar 7 at 18:23
RiggsFolly
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
add a comment |
I have a table message with following structure
id - to_number - from_number - message - send_to_podio_time
100 - +16235708320 - +16232992010 - test message1 - 2019-03-07 18:31:12
101 - +16232992010 - +16235708320 - test message2 - 2019-03-07 18:31:15
102 - +16235708786 - +16232992010 - test message3 - 2019-03-07 18:31:16
103 - +16232992010 - +16235708786 - test message4 - 2019-03-07 18:31:18
I need to get the count of conversation thread between two phone numbers
The expected output is
phone_numbers - count
+16235708320:+16232992010 - 1
+16235708786:+16232992010 - 1
Can anybody help me with the appropriate query ?
mysql group-by having sql-query-store
edited Mar 7 at 18:23
RiggsFolly
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45
add a comment |
I have a table message with following structure
id - to_number - from_number - message - send_to_podio_time
100 - +16235708320 - +16232992010 - test message1 - 2019-03-07 18:31:12
101 - +16232992010 - +16235708320 - test message2 - 2019-03-07 18:31:15
102 - +16235708786 - +16232992010 - test message3 - 2019-03-07 18:31:16
103 - +16232992010 - +16235708786 - test message4 - 2019-03-07 18:31:18
I need to get the count of conversation thread between two phone numbers
The expected output is
phone_numbers - count
+16235708320:+16232992010 - 1
+16235708786:+16232992010 - 1
Can anybody help me with the appropriate query ?
mysql group-by having sql-query-store
edited Mar 7 at 18:23
RiggsFolly
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
I have a table message with following structure
id - to_number - from_number - message - send_to_podio_time
100 - +16235708320 - +16232992010 - test message1 - 2019-03-07 18:31:12
101 - +16232992010 - +16235708320 - test message2 - 2019-03-07 18:31:15
102 - +16235708786 - +16232992010 - test message3 - 2019-03-07 18:31:16
103 - +16232992010 - +16235708786 - test message4 - 2019-03-07 18:31:18
I need to get the count of conversation thread between two phone numbers
The expected output is
phone_numbers - count
+16235708320:+16232992010 - 1
+16235708786:+16232992010 - 1
Can anybody help me with the appropriate query ?
mysql group-by having sql-query-store
mysql group-by having sql-query-store
edited Mar 7 at 18:23
RiggsFolly
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
edited Mar 7 at 18:23
RiggsFolly
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
edited Mar 7 at 18:23
RiggsFolly
72k1867113
edited Mar 7 at 18:23
RiggsFolly
72k1867113
edited Mar 7 at 18:23
RiggsFolly
72k1867113
72k1867113
asked Mar 7 at 18:12
anas koduranas kodur
15
asked Mar 7 at 18:12
anas koduranas kodur
15
asked Mar 7 at 18:12
anas koduranas kodur
15
15
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45
add a comment |
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45
add a comment |
1 Answer
1
active
oldest
votes
Try:
SELECT CONCAT_WS(':', from_number, to_number), COUNT(*)
FROM table_name
GROUP BY from_number, to_number;
This will show 4 rows as the result though, because reversed "order" between messages 1 and 2 (and similarly 3 and 4) will be treated separately.
But since you wanted "1" as the result in each row, perhaps this is what you were looking for?
answered Mar 7 at 18:26
SachemSachem
181212
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
add a comment |
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
);
);
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%2f55050314%2fmysql-group-by-with-having-clause%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
Try:
SELECT CONCAT_WS(':', from_number, to_number), COUNT(*)
FROM table_name
GROUP BY from_number, to_number;
This will show 4 rows as the result though, because reversed "order" between messages 1 and 2 (and similarly 3 and 4) will be treated separately.
But since you wanted "1" as the result in each row, perhaps this is what you were looking for?
answered Mar 7 at 18:26
SachemSachem
181212
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
add a comment |
Try:
SELECT CONCAT_WS(':', from_number, to_number), COUNT(*)
FROM table_name
GROUP BY from_number, to_number;
This will show 4 rows as the result though, because reversed "order" between messages 1 and 2 (and similarly 3 and 4) will be treated separately.
But since you wanted "1" as the result in each row, perhaps this is what you were looking for?
answered Mar 7 at 18:26
SachemSachem
181212
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
add a comment |
Try:
SELECT CONCAT_WS(':', from_number, to_number), COUNT(*)
FROM table_name
GROUP BY from_number, to_number;
This will show 4 rows as the result though, because reversed "order" between messages 1 and 2 (and similarly 3 and 4) will be treated separately.
But since you wanted "1" as the result in each row, perhaps this is what you were looking for?
answered Mar 7 at 18:26
SachemSachem
181212
Try:
SELECT CONCAT_WS(':', from_number, to_number), COUNT(*)
FROM table_name
GROUP BY from_number, to_number;
This will show 4 rows as the result though, because reversed "order" between messages 1 and 2 (and similarly 3 and 4) will be treated separately.
But since you wanted "1" as the result in each row, perhaps this is what you were looking for?
answered Mar 7 at 18:26
SachemSachem
181212
answered Mar 7 at 18:26
SachemSachem
181212
answered Mar 7 at 18:26
SachemSachem
181212
answered Mar 7 at 18:26
SachemSachem
181212
181212
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
add a comment |
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
first 2 rows are same thread. So it should be counted as 1. Like that the next 2 rows
– anas kodur
Mar 7 at 18:39
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
how do you know it's the same thread, not having a thread_id column in the table?
– Sachem
Mar 7 at 18:44
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
to achieve what you want, you should query for "from" and "to" numbers separately (not concatenated) and then you can easily filter out "duplicates" in a loop. just check if a pair or reversed pair is not already in the final array, and if it is then don't add it to it.
– Sachem
Mar 7 at 19:07
add a comment |
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.
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%2f55050314%2fmysql-group-by-with-having-clause%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
but message1 and message2 are both between same numbers. similar for message 3 and 4 so shouldn't expected result show "2" in both rows?
– Sachem
Mar 7 at 18:22
@Sachem message1 and message2 are b/w same numbers(one message thread). Like that message3 and message4 are b/w another same numbers(one message thread). So count should be 1
– anas kodur
Mar 7 at 18:26
I don't quite get this. How is this "1 message thread" if +16235708320 replied with "message2" ? :)
– Sachem
Mar 7 at 18:31
Its a conversation. Conversation between same phone numbers. I want to get each conversation separately. That is what i needed. :(
– anas kodur
Mar 7 at 18:45