-1

Is it possible to and can someone please give me a good example of selecting in one query a result set that returns counts for demographics or other things group by a certain grouping? That really sound cryptic so I am going to add an example output of what I am trying to communicate. I want a result set like:

enter image description here

So for each class a count of fields that are populated for Gender, a count of Male, a count of Female, a count of how many of race that are populated and so forth.

So something like Select curricul.class,count(stu.gender),count(stu.race),count(stu.eth) from curricul,stu group by class pivot( count(gender) for gender in (male, female)

user1417467
  • 75
  • 1
  • 6
  • You haven't told us anything about the data you're working from, the table structures, or what happens with the query you've suggested (which is in old-style syntax; with ANSI joins it would be more obvious you're missing join conditions). And why have you included an image instead of text showing what you want? It doesn't look like you want to pivot though. Are you maybe looking for `count(case ... end)` to get the male/female numbers? – Alex Poole May 20 '16 at 14:37

1 Answers1

3

You could simply use:

with curricul as
(select 1 classid, 'Math' class from dual union all
 select 2, 'Literature' from dual
)
,
 student as
( select 1 id, 1 classid, 'male' gender, 1 race, 1 eth from dual union all
    select 2, 1, 'female', 1, 2 from dual union all
    select 3, 1, 'male'  , 3, 1 from dual union all
    select 4, 1, 'male'  , 5, 7 from dual union all
    select 5, 1, 'female', 4, 8 from dual union all
    select 6, 1, 'male'  , 1, 6 from dual union all
    select 7, 2, 'female', 3, 4 from dual union all
    select 8, 2, 'female', 1, 1 from dual union all
    select 9, 2, 'female', 7, 9 from dual union all
    select 10, 2, 'male' , 9, 1 from dual union all
    select 11, 2, 'female', 8, 1 from dual
)
select s.classid, curricul.class  
    ,count(s.gender)  as count_gender
    ,sum(case when gender = 'male' then 1 else 0 end) as count_male
    ,sum(case when gender = 'female' then 1 else 0 end) as count_female
    ,count(s.race)  as count_race
    ,count(s.eth) as count_ethnicity 
from student s
inner join curricul 
    on s.classid = curricul.classid
group by s.classid, curricul.class ;
Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42
  • 2
    If gender is nullable then `count(gender)` and `count(*)` can give different results. Similarly you might not want the distinct in your counts - it may just be counting how many are populated, esp. given the numbers in the example. Actually, it says that in the question, in the second-to-last paragraph. – Alex Poole May 20 '16 at 16:17
  • @AlexPoole ah, thank you for nullable and explaination – Pham X. Bach May 20 '16 at 16:27