Oracle 8i Query Help

Question

Hi all,

I have a table that looks something like this:

     ID    |     STATUS    |     TYPE
----------------------------------------
x123       |      A        | High School
x122       |      I        | High School
x124       |      F        | High School
x125       |      A        | College
x126       |      I        | College
x127       |      F        | College
x128       |      F        | College

Can anyone help me come up with a query for oracle 8i that displays this table like this

Type             |    Count A    |    Count I    |  Count F
------------------------------------------------------------
High School      |     1         |     1         |  1
College          |     1         |     1         |  2

Thanks!

Answer 1

Here's one approach:

select t.type                                          as "Type"
     , sum(case when t.status = 'A' then 1 else 0 end) as "Count A"
     , sum(case when t.status = 'I' then 1 else 0 end) as "Count I"
     , sum(case when t.status = 'F' then 1 else 0 end) as "Count F"
  from my_table t
 group by t.type
 order by t.type desc

This works if you have specific columns you want returned, and works for "counting" rows that meet more complex criteria set.

[EDIT]

(Added the DESC keyword to get the result set ordered as shown by OP, +1 good catch by Rob van Wijk!)

(Andomar makes a good observation, with more and more columns in the result set, using this approach, the statement gets unweildy. There are other approaches to getting the same result set which work well if the only "test" is an equality comparison on a single column.)

Oracle 8i does support the CASE expression, doesn't it? Oracle 8 didn't, if I recall correctly. We can go "old school" to do the same thing with the DECODE function:

select t.type                        as "Type"
     , sum(decode(t.status,'A',1,0)) as "Count A"
     , sum(decode(t.status,'I',1,0)) as "Count I"
     , sum(decode(t.status,'F',1,0)) as "Count F"
  from my_table t
 group by t.type
 order by t.type DESC

[/EDIT]

Sometimes, we want to check more than one type condition, and include a row in more than one count. We can get a total

select t.type                                             as "Type"
     , sum(case when t.status in ('A') then 1 else 0 end) as "Count A"
     , sum(case when t.status in ('I') then 1 else 0 end) as "Count I"
     , sum(case when t.status in ('F') then 1 else 0 end) as "Count F"
     , sum(case when t.status in ('A','I') then 1 else 0 end) as "#AI"
     , sum(decode(sign(t.foo-t.bar),1,1,0))               as "#foo>bar"
     , sum(decode(sign(10.0-t.foo),1,1,0))                as "#foo<10"
  from my_table t
 group by t.type
 order by t.type desc

(Just to point out, it's possible for a row to satisfy the specified criteria for multiple columns, and so it could be "counted" more than once. Sometimes, that's exactly what we want.)