Re: two total from a Select

On 23 mayo, 21:18, ErikYkema <erik.yk..._at_gmail.com> wrote:
> On May 21, 11:28 am,valigula<valig..._at_gmail.com> wrote:
>
> > I need to calculate two differents totals from a table . One is
> > filtering by the year to know how many from this year and the other is
> > a total without that filter.
> > i am using oracle 9i. At the moment i am trying to use the rollup
> > function.
> > Is it more apropiate to use a subquery???
>
> > thanks
>
> > A
>
> Hi,
> Sum will work over nulls, so if you replace values that do not comply
> with the filter condition with null, you can get your requirement
> fulfilled, assuming I have understood it correctly.
>
> select object_type
> , sum(object_id) as total_without_filter
> , sum(
> case
> when not (object_type = 'TABLE') then null
> else object_id
> end
> ) as total_with_filter
> from all_objects
> group by object_type
>
> (Of course, summing over object_id is non sensical and selecting from
> all_objects not very elegant.)
> Good luck and please come back when you need more help.
> Regards,
> Erik Ykema

Thanks for your answer .. i managed to do it using ... .....
 COUNT (*) total1,
 COUNT (DECODE (table1_filtered.anyoalta, year, year)) total2, ...
I would have a look to tour sentence as well . ;O) ty

A. Received on Fri May 25 2007 - 02:52:34 CDT