Re: Subtracting from a set of rows/columns

From: <jan.stulrajter_at_zoznam.sk>
Date: 14 Mar 2007 02:14:47 -0700
Message-ID: <1173863687.045086.202910@n59g2000hsh.googlegroups.com>

SELECT

   CASE WHEN sort_number <>0 THEN COUNT(col1) END COUNT1,
   CASE WHEN sort_number <>0 THEN COUNT(col2) END COUNT2,
   CASE WHEN sort_number <>0 THEN COUNT(col3) END COUNT3,
   CASE WHEN sort_number <>0 THEN COUNT(col4) END COUNT4,
   CASE WHEN sort_number <>0 THEN COUNT(col5) END COUNT5

FROM test_counters

   WHERE test_id=:p_test_id;

It will not Count the rows with NULL in col1, col2 etc...

Jan

Martin T. napísal(a):
> Greetings,
>
> The following situation:
> TABLE test_counters ...
> SORT_NUMBER | TEST_ID | COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5
>
> I need to subtract from all counts of a test_id the values of the
> respective count columns for sort_number zero.
> (ie: count* = count* - count*_where_sort_number_zero)
>
> I have implemented it now as follows:
> ---
> v_counts test_counter%rowtype;
> ...
> SELECT *
> INTO v_counts
> FROM test_counters
> WHERE SORT_NUMBER=0
> and TEST_ID = :p_new_id;
>
> UPDATE MEASURES
> SET COUNT1 = COUNT1 - v_counts.count1,
> COUNT2 = COUNT2 - v_counts.count2,
> COUNT3 = COUNT3 - v_counts.count3,
> COUNT4 = COUNT4 - v_counts.count4,
> COUNT5 = COUNT5 - v_counts.count5
> WHERE ORDER_ID = p_new_order_id;
> ---
>
> Is there a more elegant way to do it?
>
> cheers,
> Martin
Received on Wed Mar 14 2007 - 04:14:47 CDT