Re: group by problem!!!

Hi,

> i'd like to obtain on the same line of my sql select query:
>
> select sector,count(event) closed,count(event) all_events
> from table
> group by sector
>
> but all_events is for all events of course (no where clause on the
> status) and closed is only for events with status 'C'
>
> i did it with a union select but i obtain 2 lines...
>

try it with views which give back only 1 dataset and don't join it (Cartesian Product), for example

select vw1.c as opened_events, vw2.c as closed_events, vw1.c+vw2.c as all_events
from

    (select count(event) as c

     from table
     group by sector
     where status='O') vw1,
    (select count(event) as c
     from table
     group by sector
     where status='C') vw2

Regards,
Falko Rotter

• Software Developer -

Rotter & Kalweit Softwaredesign GbR
Friemarer Straße 38

99867 Gotha
GERMANY Received on Fri Aug 17 2001 - 02:55:45 CDT