how do I count the data plus list the data
up vote
0
down vote
favorite
I am very very new to SQL
For example, a student with 2 As and 3 Cs will produce two records: sid, A, 2; sid, C, 3. If the student doesn't have any grades I don't want them listed
select sid, count(grade) from enrollments
group by sid
Example of what I want:
sid grade
123 A,2; C,3
456 A,3; B,1
sql oracle
edited Nov 9 at 19:26
Littlefoot
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
add a comment |
up vote
0
down vote
favorite
I am very very new to SQL
For example, a student with 2 As and 3 Cs will produce two records: sid, A, 2; sid, C, 3. If the student doesn't have any grades I don't want them listed
select sid, count(grade) from enrollments
group by sid
Example of what I want:
sid grade
123 A,2; C,3
456 A,3; B,1
sql oracle
edited Nov 9 at 19:26
Littlefoot
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am very very new to SQL
For example, a student with 2 As and 3 Cs will produce two records: sid, A, 2; sid, C, 3. If the student doesn't have any grades I don't want them listed
select sid, count(grade) from enrollments
group by sid
Example of what I want:
sid grade
123 A,2; C,3
456 A,3; B,1
sql oracle
edited Nov 9 at 19:26
Littlefoot
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
I am very very new to SQL
For example, a student with 2 As and 3 Cs will produce two records: sid, A, 2; sid, C, 3. If the student doesn't have any grades I don't want them listed
select sid, count(grade) from enrollments
group by sid
Example of what I want:
sid grade
123 A,2; C,3
456 A,3; B,1
sql oracle
sql oracle
edited Nov 9 at 19:26
Littlefoot
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
edited Nov 9 at 19:26
Littlefoot
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
edited Nov 9 at 19:26
Littlefoot
18.1k51333
edited Nov 9 at 19:26
Littlefoot
18.1k51333
edited Nov 9 at 19:26
Littlefoot
18.1k51333
18.1k51333
asked Nov 9 at 19:20
Hollywoodhd
42
asked Nov 9 at 19:20
Hollywoodhd
42
asked Nov 9 at 19:20
Hollywoodhd
42
42
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
I think this should work for you.
select sid,
listagg(gradecount, '; ') within group (order by gradecount) as grade
from (
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
) studentgradecount
group by sid;
If it seems confusing, try running the inner query by itself to see what it does:
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
answered Nov 9 at 20:33
kfinity
2,6341514
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
I think this should work for you.
select sid,
listagg(gradecount, '; ') within group (order by gradecount) as grade
from (
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
) studentgradecount
group by sid;
If it seems confusing, try running the inner query by itself to see what it does:
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
answered Nov 9 at 20:33
kfinity
2,6341514
add a comment |
up vote
1
down vote
I think this should work for you.
select sid,
listagg(gradecount, '; ') within group (order by gradecount) as grade
from (
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
) studentgradecount
group by sid;
If it seems confusing, try running the inner query by itself to see what it does:
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
answered Nov 9 at 20:33
kfinity
2,6341514
add a comment |
up vote
1
down vote
up vote
1
down vote
I think this should work for you.
select sid,
listagg(gradecount, '; ') within group (order by gradecount) as grade
from (
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
) studentgradecount
group by sid;
If it seems confusing, try running the inner query by itself to see what it does:
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
answered Nov 9 at 20:33
kfinity
2,6341514
I think this should work for you.
select sid,
listagg(gradecount, '; ') within group (order by gradecount) as grade
from (
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
) studentgradecount
group by sid;
If it seems confusing, try running the inner query by itself to see what it does:
select sid, grade || ',' || count(1) as gradecount
from enrollments
where grade is not null
group by sid, grade
answered Nov 9 at 20:33
kfinity
2,6341514
answered Nov 9 at 20:33
kfinity
2,6341514
answered Nov 9 at 20:33
kfinity
2,6341514
answered Nov 9 at 20:33
kfinity
2,6341514
2,6341514
add a comment |
add a comment |
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%2f53232053%2fhow-do-i-count-the-data-plus-list-the-data%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
