How to create a group based on multiple different columns in SQL?
up vote
-2
down vote
favorite
I have a dataset that has six different columns (UserID, School and four different Tests) as below:
UserID School T1 T2 T3 T4 Group
M1 A 1 3 G1
M2 C 3 G1
M3 A 5 G1
M4 B 9 7 G2
M5 D 2 G3
M6 D 4 G3
M7 A G1
M8 E 6 2 G3
M9 F 1 G1
M10 B 8 G2
I want to add a calculated Group column using the first six columns, the corresponding values for this new Group column have shown in the example.
I want to assign a Group Number to each UserID in Group Column. The users will be in same group if they have at least one similar test (test type is not important) or school. e.g. M5 and M6 are in same group because they have same school and M8 is also in G3 because it has common test(2) with M6.
sql
asked Nov 21 at 21:24
Famrin
93
|
show 2 more comments
up vote
-2
down vote
favorite
I have a dataset that has six different columns (UserID, School and four different Tests) as below:
UserID School T1 T2 T3 T4 Group
M1 A 1 3 G1
M2 C 3 G1
M3 A 5 G1
M4 B 9 7 G2
M5 D 2 G3
M6 D 4 G3
M7 A G1
M8 E 6 2 G3
M9 F 1 G1
M10 B 8 G2
I want to add a calculated Group column using the first six columns, the corresponding values for this new Group column have shown in the example.
I want to assign a Group Number to each UserID in Group Column. The users will be in same group if they have at least one similar test (test type is not important) or school. e.g. M5 and M6 are in same group because they have same school and M8 is also in G3 because it has common test(2) with M6.
sql
asked Nov 21 at 21:24
Famrin
93
Unsure of what you're asking. Are you saying that you want add a calculatedGroupcolumn, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this newGroupcolumn should be? If so, I'm not seeing how you're arriving at the values for theGroupcolumn. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray
– Bob Kaufman
Nov 21 at 21:29
2
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
1
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55
|
show 2 more comments
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
I have a dataset that has six different columns (UserID, School and four different Tests) as below:
UserID School T1 T2 T3 T4 Group
M1 A 1 3 G1
M2 C 3 G1
M3 A 5 G1
M4 B 9 7 G2
M5 D 2 G3
M6 D 4 G3
M7 A G1
M8 E 6 2 G3
M9 F 1 G1
M10 B 8 G2
I want to add a calculated Group column using the first six columns, the corresponding values for this new Group column have shown in the example.
I want to assign a Group Number to each UserID in Group Column. The users will be in same group if they have at least one similar test (test type is not important) or school. e.g. M5 and M6 are in same group because they have same school and M8 is also in G3 because it has common test(2) with M6.
sql
asked Nov 21 at 21:24
Famrin
93
I have a dataset that has six different columns (UserID, School and four different Tests) as below:
UserID School T1 T2 T3 T4 Group
M1 A 1 3 G1
M2 C 3 G1
M3 A 5 G1
M4 B 9 7 G2
M5 D 2 G3
M6 D 4 G3
M7 A G1
M8 E 6 2 G3
M9 F 1 G1
M10 B 8 G2
I want to add a calculated Group column using the first six columns, the corresponding values for this new Group column have shown in the example.
I want to assign a Group Number to each UserID in Group Column. The users will be in same group if they have at least one similar test (test type is not important) or school. e.g. M5 and M6 are in same group because they have same school and M8 is also in G3 because it has common test(2) with M6.
sql
sql
asked Nov 21 at 21:24
Famrin
93
asked Nov 21 at 21:24
Famrin
93
edited Nov 21 at 23:06
asked Nov 21 at 21:24
Famrin
93
asked Nov 21 at 21:24
Famrin
93
asked Nov 21 at 21:24
Famrin
93
93
Unsure of what you're asking. Are you saying that you want add a calculatedGroupcolumn, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this newGroupcolumn should be? If so, I'm not seeing how you're arriving at the values for theGroupcolumn. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray
– Bob Kaufman
Nov 21 at 21:29
2
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
1
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55
|
show 2 more comments
Unsure of what you're asking. Are you saying that you want add a calculatedGroupcolumn, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this newGroupcolumn should be? If so, I'm not seeing how you're arriving at the values for theGroupcolumn. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray
– Bob Kaufman
Nov 21 at 21:29
2
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
1
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55
Unsure of what you're asking. Are you saying that you want add a calculated
Group column, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this new Group column should be? If so, I'm not seeing how you're arriving at the values for the Group column. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray– Bob Kaufman
Nov 21 at 21:29
Unsure of what you're asking. Are you saying that you want add a calculated
Group column, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this new Group column should be? If so, I'm not seeing how you're arriving at the values for the Group column. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray– Bob Kaufman
Nov 21 at 21:29
2
2
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
1
1
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55
|
show 2 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53420657%2fhow-to-create-a-group-based-on-multiple-different-columns-in-sql%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
Unsure of what you're asking. Are you saying that you want add a calculated
Groupcolumn, and that based on the sample data you've provided for the first six columns, you've shown what the corresponding values for this newGroupcolumn should be? If so, I'm not seeing how you're arriving at the values for theGroupcolumn. Please edit your question to include more of an explanation, or respond to my comment letting me know where my assumptions have led me astray– Bob Kaufman
Nov 21 at 21:29
2
mysql <> sql server. Pick the one you actually use as the each uses their own sql dialect.
– SMor
Nov 21 at 21:32
Removed conflicting product tags, pls add the one back that you actually use.
– Shadow
Nov 21 at 21:46
@BobKaufman Thanks for your comment. I added more explanation to my question.
– Famrin
Nov 21 at 21:50
1
Why is M4 in G2, if it shares test 3 with M2, which is in G1?
– Shadow
Nov 21 at 21:55