CODEWITHSUNDEEP
sqloracle-database
Hollywoodhd
Hollywoodhd

Reputation: 9

how do I count the data plus list the data

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

Upvotes: 0

Views: 46

Answers (1)

kfinity
kfinity

Reputation: 9091

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

Upvotes: 1

Related Questions