Hi, I'm using Ingres 9.0.4 and have the following table (I've
simplified this to make it easier to explain):

create table students
(student_id char(6) not null with default,
module_id char(6) not null with default,
stud_status char(1) not null with default)

stud_status has values of A,B + C.

I want to compose a query to give me a count of students on the module
and how many at each status.
e.g.

Module Total Count A Count B Count C Count
T101 50 25 10 15
A444 120 65 44 11


I can get part way there by doing the following:

select module_id, count(*) as total_count
from students
group by module_id

Which gives the count of students for each module (first two columns
required).


I can also get the count of A,B+C statuses by module individually

select module_id, count(*) as a_count
from students
where stud_status='A'
group by module_id ;

select module_id, count(*) as b_count
from students
where stud_status='B'
group by module_id ;

select module_id, count(*) as c_count
from students
where stud_status='C'
group by module_id ;


Then I can programmatically merge all this data into the format I need
(or use some temporary tables).

But is there a more elegant way I can retrieve this data that I'm
missing? Can the case expression some how help me? Or maybe sub queries?

In an ideal world I'd run like to run a single query to get this data in
one hit.

Any thoughts welcome.

Cheers,

Simon

On Nov 3, 2010, at 12:07 PM, Simon Roust wrote:

select module_id, count(*) as total_count,
sum(case when stud_status = 'A' then 1 else 0 end) as a_count,
... repeat for b and c
from students
group by module_id

I haven't tried this but it ought to work.

Karl

On 03/11/2010 16:40, Karl Schendel wrote:

It works extremely well and is most compact too. Thanks very much!