Re: two total from a Select

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 Received on Wed May 23 2007 - 14:18:38 CDT