joining same table without a common field
0
Oracle 11g/SQLPLUS
Given single table TABLE with three fields ID, PARM, VALUE, I want to select and group by based on different values of VALUE
E.g.
ID PARM VALUE
id1 accnt France-1
id2 model Type-1
id3 field blah
id4 accnt France-2
id5 model Type-1
id6 field blah
id7 accnt France-2
id8 model Type-2
id9 field blah
id10 accnt Germany-1
id11 model Type-1
id12 field blah
id13 accnt Germany-2
id14 model Type-1
id15 field blah
I want to count the number of models by substr(accnt)
German Type-1 2
France Type-1 2
France Type-2 1
I have something like this but I don't know how to JOIN T1 and T2 as there is no common field.
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2
where T1.parm='accnt'
and T2.parm='model'
group by substr(T1.value, 1, 6), T2.value
oracle
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
Oracle 11g/SQLPLUS
Given single table TABLE with three fields ID, PARM, VALUE, I want to select and group by based on different values of VALUE
E.g.
ID PARM VALUE
id1 accnt France-1
id2 model Type-1
id3 field blah
id4 accnt France-2
id5 model Type-1
id6 field blah
id7 accnt France-2
id8 model Type-2
id9 field blah
id10 accnt Germany-1
id11 model Type-1
id12 field blah
id13 accnt Germany-2
id14 model Type-1
id15 field blah
I want to count the number of models by substr(accnt)
German Type-1 2
France Type-1 2
France Type-2 1
I have something like this but I don't know how to JOIN T1 and T2 as there is no common field.
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2
where T1.parm='accnt'
and T2.parm='model'
group by substr(T1.value, 1, 6), T2.value
oracle
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
0
0
Oracle 11g/SQLPLUS
Given single table TABLE with three fields ID, PARM, VALUE, I want to select and group by based on different values of VALUE
E.g.
ID PARM VALUE
id1 accnt France-1
id2 model Type-1
id3 field blah
id4 accnt France-2
id5 model Type-1
id6 field blah
id7 accnt France-2
id8 model Type-2
id9 field blah
id10 accnt Germany-1
id11 model Type-1
id12 field blah
id13 accnt Germany-2
id14 model Type-1
id15 field blah
I want to count the number of models by substr(accnt)
German Type-1 2
France Type-1 2
France Type-2 1
I have something like this but I don't know how to JOIN T1 and T2 as there is no common field.
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2
where T1.parm='accnt'
and T2.parm='model'
group by substr(T1.value, 1, 6), T2.value
oracle
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
Oracle 11g/SQLPLUS
Given single table TABLE with three fields ID, PARM, VALUE, I want to select and group by based on different values of VALUE
E.g.
ID PARM VALUE
id1 accnt France-1
id2 model Type-1
id3 field blah
id4 accnt France-2
id5 model Type-1
id6 field blah
id7 accnt France-2
id8 model Type-2
id9 field blah
id10 accnt Germany-1
id11 model Type-1
id12 field blah
id13 accnt Germany-2
id14 model Type-1
id15 field blah
I want to count the number of models by substr(accnt)
German Type-1 2
France Type-1 2
France Type-2 1
I have something like this but I don't know how to JOIN T1 and T2 as there is no common field.
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2
where T1.parm='accnt'
and T2.parm='model'
group by substr(T1.value, 1, 6), T2.value
oracle
oracle
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
edited Jan 15 '15 at 13:53
ypercubeᵀᴹ
76k11129212
76k11129212
asked Jan 15 '15 at 3:11
user56636user56636
11
asked Jan 15 '15 at 3:11
user56636user56636
11
asked Jan 15 '15 at 3:11
user56636user56636
11
11
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
TABLE Table was actually linked via another TABLE Keys, so needed to add more clauses
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2, Keys K1, Keys K2
where T1.parm='accnt'
and T2.parm='model'
and T1.id=K1.id
and T2.id=K2.id
and K1.id=K2.id -- this is the join
group by substr(T1.value, 1, 6), T2.value;
answered Jan 15 '15 at 4:47
user56636user56636
11
I doubt that the actual query you used has:and K1.id=K2.idIt's probably a join on some other column, notid
– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:'USA-1','USA-2'), or different countries that their first 6 characters are identical.
– ypercubeᵀᴹ
Jan 15 '15 at 13:55
add a comment |
0
Try the below one:
SELECT substr (value, 1, 6) country
,value2
,count (*) cnt
FROM (SELECT ID
,param
,value
,lead (value) OVER (ORDER BY 1) value2
FROM tmp)
WHERE param = 'accnt'
GROUP BY substr (value, 1, 6), value2;
Output:
COUNTRY VALUE2 CNT
------- ------ -----
France Type-2 1
German Type-1 2
France Type-1 2
3 rows selected.
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f89331%2fjoining-same-table-without-a-common-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
TABLE Table was actually linked via another TABLE Keys, so needed to add more clauses
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2, Keys K1, Keys K2
where T1.parm='accnt'
and T2.parm='model'
and T1.id=K1.id
and T2.id=K2.id
and K1.id=K2.id -- this is the join
group by substr(T1.value, 1, 6), T2.value;
answered Jan 15 '15 at 4:47
user56636user56636
11
I doubt that the actual query you used has:and K1.id=K2.idIt's probably a join on some other column, notid
– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:'USA-1','USA-2'), or different countries that their first 6 characters are identical.
– ypercubeᵀᴹ
Jan 15 '15 at 13:55
add a comment |
0
TABLE Table was actually linked via another TABLE Keys, so needed to add more clauses
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2, Keys K1, Keys K2
where T1.parm='accnt'
and T2.parm='model'
and T1.id=K1.id
and T2.id=K2.id
and K1.id=K2.id -- this is the join
group by substr(T1.value, 1, 6), T2.value;
answered Jan 15 '15 at 4:47
user56636user56636
11
I doubt that the actual query you used has:and K1.id=K2.idIt's probably a join on some other column, notid
– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:'USA-1','USA-2'), or different countries that their first 6 characters are identical.
– ypercubeᵀᴹ
Jan 15 '15 at 13:55
add a comment |
0
0
0
TABLE Table was actually linked via another TABLE Keys, so needed to add more clauses
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2, Keys K1, Keys K2
where T1.parm='accnt'
and T2.parm='model'
and T1.id=K1.id
and T2.id=K2.id
and K1.id=K2.id -- this is the join
group by substr(T1.value, 1, 6), T2.value;
answered Jan 15 '15 at 4:47
user56636user56636
11
TABLE Table was actually linked via another TABLE Keys, so needed to add more clauses
select substr(T1.value, 1, 6), T2.value, count(*)
from Table T1, Table T2, Keys K1, Keys K2
where T1.parm='accnt'
and T2.parm='model'
and T1.id=K1.id
and T2.id=K2.id
and K1.id=K2.id -- this is the join
group by substr(T1.value, 1, 6), T2.value;
answered Jan 15 '15 at 4:47
user56636user56636
11
answered Jan 15 '15 at 4:47
user56636user56636
11
answered Jan 15 '15 at 4:47
user56636user56636
11
answered Jan 15 '15 at 4:47
user56636user56636
11
11
I doubt that the actual query you used has:and K1.id=K2.idIt's probably a join on some other column, notid
– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:'USA-1','USA-2'), or different countries that their first 6 characters are identical.
– ypercubeᵀᴹ
Jan 15 '15 at 13:55
add a comment |
I doubt that the actual query you used has:and K1.id=K2.idIt's probably a join on some other column, notid
– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:'USA-1','USA-2'), or different countries that their first 6 characters are identical.
– ypercubeᵀᴹ
Jan 15 '15 at 13:55
I doubt that the actual query you used has:
and K1.id=K2.id It's probably a join on some other column, not id– ypercubeᵀᴹ
Jan 15 '15 at 13:49
I doubt that the actual query you used has:
and K1.id=K2.id It's probably a join on some other column, not id– ypercubeᵀᴹ
Jan 15 '15 at 13:49
And the query will break when you have a country with a name of 4 or less characters (example values:
'USA-1', 'USA-2'), or different countries that their first 6 characters are identical.– ypercubeᵀᴹ
Jan 15 '15 at 13:55
And the query will break when you have a country with a name of 4 or less characters (example values:
'USA-1', 'USA-2'), or different countries that their first 6 characters are identical.– ypercubeᵀᴹ
Jan 15 '15 at 13:55
add a comment |
0
Try the below one:
SELECT substr (value, 1, 6) country
,value2
,count (*) cnt
FROM (SELECT ID
,param
,value
,lead (value) OVER (ORDER BY 1) value2
FROM tmp)
WHERE param = 'accnt'
GROUP BY substr (value, 1, 6), value2;
Output:
COUNTRY VALUE2 CNT
------- ------ -----
France Type-2 1
German Type-1 2
France Type-1 2
3 rows selected.
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
add a comment |
0
Try the below one:
SELECT substr (value, 1, 6) country
,value2
,count (*) cnt
FROM (SELECT ID
,param
,value
,lead (value) OVER (ORDER BY 1) value2
FROM tmp)
WHERE param = 'accnt'
GROUP BY substr (value, 1, 6), value2;
Output:
COUNTRY VALUE2 CNT
------- ------ -----
France Type-2 1
German Type-1 2
France Type-1 2
3 rows selected.
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
add a comment |
0
0
0
Try the below one:
SELECT substr (value, 1, 6) country
,value2
,count (*) cnt
FROM (SELECT ID
,param
,value
,lead (value) OVER (ORDER BY 1) value2
FROM tmp)
WHERE param = 'accnt'
GROUP BY substr (value, 1, 6), value2;
Output:
COUNTRY VALUE2 CNT
------- ------ -----
France Type-2 1
German Type-1 2
France Type-1 2
3 rows selected.
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
Try the below one:
SELECT substr (value, 1, 6) country
,value2
,count (*) cnt
FROM (SELECT ID
,param
,value
,lead (value) OVER (ORDER BY 1) value2
FROM tmp)
WHERE param = 'accnt'
GROUP BY substr (value, 1, 6), value2;
Output:
COUNTRY VALUE2 CNT
------- ------ -----
France Type-2 1
German Type-1 2
France Type-1 2
3 rows selected.
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
answered Jan 15 '15 at 5:29
MoazzamMoazzam
111
111
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f89331%2fjoining-same-table-without-a-common-field%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
