Mixing
SQL doesn't work using a column returns from subquery in group by
I need to run a query like below but it doesn't recognize SHOWNDATE. Why this is happening?
The query was simplified to be clear:
select PYNAME,
SHOWNDATE=(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID),
count(PYSUBJECTID)
from B
group by PYNAME,
SHOWNDATE
sql oracle group-by subquery
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
add a comment |
I need to run a query like below but it doesn't recognize SHOWNDATE. Why this is happening?
The query was simplified to be clear:
select PYNAME,
SHOWNDATE=(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID),
count(PYSUBJECTID)
from B
group by PYNAME,
SHOWNDATE
sql oracle group-by subquery
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
add a comment |
I need to run a query like below but it doesn't recognize SHOWNDATE. Why this is happening?
The query was simplified to be clear:
select PYNAME,
SHOWNDATE=(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID),
count(PYSUBJECTID)
from B
group by PYNAME,
SHOWNDATE
sql oracle group-by subquery
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
I need to run a query like below but it doesn't recognize SHOWNDATE. Why this is happening?
The query was simplified to be clear:
select PYNAME,
SHOWNDATE=(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID),
count(PYSUBJECTID)
from B
group by PYNAME,
SHOWNDATE
sql oracle group-by subquery
sql oracle group-by subquery
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
edited Nov 20 '18 at 18:26
Littlefoot
21.2k71533
21.2k71533
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
asked Nov 20 '18 at 16:19
Ali TorAli Tor
1,0121128
1,0121128
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
I think On Select clause write AS SHOWNDATE
SHOWNDATE = is not right.
select
PYNAME,
(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) AS SHOWNDATE,
count(PYSUBJECTID)
from B
group by PYNAME,SHOWNDATE
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
add a comment |
Here's an example based on Scott's schema which shows how to do that:
SQL> select b.deptno,
2 (select max(a.sal) from emp a where b.deptno = a.deptno) as showsal,
3 count(b.empno)
4 from emp b
5 group by b.deptno;
DEPTNO SHOWSAL COUNT(B.EMPNO)
---------- ---------- --------------
30 2850 6
20 3000 3
10 5000 3
SQL>
Or, applied to your query:
select b.pyname,
(select max(a.outcometime) from a where a.id = b.id) showndate,
count(b.pysubjectid)
from b
group by b.pyname;
Don't forget to apply table aliases to all columns!
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
add a comment |
ALIAS (in this case SHOWNDATE) is not a valid identifier but it is for the result set from the subquery. ALIASES are used for convenience...no one wants "SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID" as column name.
select
PYNAME
,SHOWNDATE
,COUNT
from (
select PYNAME
,(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) SHOWNDATE
,count(PYSUBJECTID) COUNT
from B
Group by PYNAME
)
group by PYNAME
,SHOWNDATE
,COUNT
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
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
});
}
});
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%2f53397232%2fsql-doesnt-work-using-a-column-returns-from-subquery-in-group-by%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think On Select clause write AS SHOWNDATE
SHOWNDATE = is not right.
select
PYNAME,
(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) AS SHOWNDATE,
count(PYSUBJECTID)
from B
group by PYNAME,SHOWNDATE
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
add a comment |
I think On Select clause write AS SHOWNDATE
SHOWNDATE = is not right.
select
PYNAME,
(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) AS SHOWNDATE,
count(PYSUBJECTID)
from B
group by PYNAME,SHOWNDATE
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
add a comment |
I think On Select clause write AS SHOWNDATE
SHOWNDATE = is not right.
select
PYNAME,
(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) AS SHOWNDATE,
count(PYSUBJECTID)
from B
group by PYNAME,SHOWNDATE
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
I think On Select clause write AS SHOWNDATE
SHOWNDATE = is not right.
select
PYNAME,
(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) AS SHOWNDATE,
count(PYSUBJECTID)
from B
group by PYNAME,SHOWNDATE
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
answered Nov 20 '18 at 16:28
László TóthLászló Tóth
765
765
add a comment |
add a comment |
Here's an example based on Scott's schema which shows how to do that:
SQL> select b.deptno,
2 (select max(a.sal) from emp a where b.deptno = a.deptno) as showsal,
3 count(b.empno)
4 from emp b
5 group by b.deptno;
DEPTNO SHOWSAL COUNT(B.EMPNO)
---------- ---------- --------------
30 2850 6
20 3000 3
10 5000 3
SQL>
Or, applied to your query:
select b.pyname,
(select max(a.outcometime) from a where a.id = b.id) showndate,
count(b.pysubjectid)
from b
group by b.pyname;
Don't forget to apply table aliases to all columns!
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
add a comment |
Here's an example based on Scott's schema which shows how to do that:
SQL> select b.deptno,
2 (select max(a.sal) from emp a where b.deptno = a.deptno) as showsal,
3 count(b.empno)
4 from emp b
5 group by b.deptno;
DEPTNO SHOWSAL COUNT(B.EMPNO)
---------- ---------- --------------
30 2850 6
20 3000 3
10 5000 3
SQL>
Or, applied to your query:
select b.pyname,
(select max(a.outcometime) from a where a.id = b.id) showndate,
count(b.pysubjectid)
from b
group by b.pyname;
Don't forget to apply table aliases to all columns!
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
add a comment |
Here's an example based on Scott's schema which shows how to do that:
SQL> select b.deptno,
2 (select max(a.sal) from emp a where b.deptno = a.deptno) as showsal,
3 count(b.empno)
4 from emp b
5 group by b.deptno;
DEPTNO SHOWSAL COUNT(B.EMPNO)
---------- ---------- --------------
30 2850 6
20 3000 3
10 5000 3
SQL>
Or, applied to your query:
select b.pyname,
(select max(a.outcometime) from a where a.id = b.id) showndate,
count(b.pysubjectid)
from b
group by b.pyname;
Don't forget to apply table aliases to all columns!
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
Here's an example based on Scott's schema which shows how to do that:
SQL> select b.deptno,
2 (select max(a.sal) from emp a where b.deptno = a.deptno) as showsal,
3 count(b.empno)
4 from emp b
5 group by b.deptno;
DEPTNO SHOWSAL COUNT(B.EMPNO)
---------- ---------- --------------
30 2850 6
20 3000 3
10 5000 3
SQL>
Or, applied to your query:
select b.pyname,
(select max(a.outcometime) from a where a.id = b.id) showndate,
count(b.pysubjectid)
from b
group by b.pyname;
Don't forget to apply table aliases to all columns!
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
answered Nov 20 '18 at 18:30
LittlefootLittlefoot
21.2k71533
21.2k71533
add a comment |
add a comment |
ALIAS (in this case SHOWNDATE) is not a valid identifier but it is for the result set from the subquery. ALIASES are used for convenience...no one wants "SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID" as column name.
select
PYNAME
,SHOWNDATE
,COUNT
from (
select PYNAME
,(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) SHOWNDATE
,count(PYSUBJECTID) COUNT
from B
Group by PYNAME
)
group by PYNAME
,SHOWNDATE
,COUNT
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
add a comment |
ALIAS (in this case SHOWNDATE) is not a valid identifier but it is for the result set from the subquery. ALIASES are used for convenience...no one wants "SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID" as column name.
select
PYNAME
,SHOWNDATE
,COUNT
from (
select PYNAME
,(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) SHOWNDATE
,count(PYSUBJECTID) COUNT
from B
Group by PYNAME
)
group by PYNAME
,SHOWNDATE
,COUNT
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
add a comment |
ALIAS (in this case SHOWNDATE) is not a valid identifier but it is for the result set from the subquery. ALIASES are used for convenience...no one wants "SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID" as column name.
select
PYNAME
,SHOWNDATE
,COUNT
from (
select PYNAME
,(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) SHOWNDATE
,count(PYSUBJECTID) COUNT
from B
Group by PYNAME
)
group by PYNAME
,SHOWNDATE
,COUNT
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
ALIAS (in this case SHOWNDATE) is not a valid identifier but it is for the result set from the subquery. ALIASES are used for convenience...no one wants "SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID" as column name.
select
PYNAME
,SHOWNDATE
,COUNT
from (
select PYNAME
,(SELECT MAX(OUTCOMETIME) FROM A where A.ID=B.ID) SHOWNDATE
,count(PYSUBJECTID) COUNT
from B
Group by PYNAME
)
group by PYNAME
,SHOWNDATE
,COUNT
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
edited Nov 21 '18 at 13:24
Robert Andrzejuk
2,74921424
2,74921424
answered Nov 20 '18 at 20:30
user3439907user3439907
112
answered Nov 20 '18 at 20:30
user3439907user3439907
112
answered Nov 20 '18 at 20:30
user3439907user3439907
112
112
add a comment |
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%2f53397232%2fsql-doesnt-work-using-a-column-returns-from-subquery-in-group-by%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
