Sql tally

From PTAGISWiki

How to tally data

A working example of how to do this is in the site tally app (sockeye://home/ptagdev/ptagis3/source/site_tally_app)

Here is a sample script from that application:

#! /bin/ksh
#
# bo2_tally_ytd.sh
#
# Purpose:  Retrieves year-to-date aggregate data for a specific site,
#           for the year specified by $YEAR
#
# When         Who                      What
# ---------    ---------------------    -----------------------------------
# Generated by STG_genTallyScripts.pl on Friday, 23 March 2007, at 12:56:03
#

sql $DBNAME <<EOFxx
modify bo2_tally to truncated;
commit;
\p\g
set lockmode on site_load_event where maxlocks=40;
commit;
\p\g

insert into bo2_tally
   (
     species
   , run
   , rearing_type
   , bo2_river
   )
    
    SELECT

     species        = ifnull(t_species,' ')
   , run            = ifnull(t_run,' ')
   , rearing_type   = ifnull(t_rear_type,' ')

   , bo2_river      = ifnull(sum(
                      abs((locate(last_coil,'01')-3)/2) +
                      abs((locate(last_coil,'02')-3)/2) +
                      abs((locate(last_coil,'03')-3)/2) +
                      abs((locate(last_coil,'04')-3)/2) +
                      abs((locate(last_coil,'05')-3)/2) +
                      abs((locate(last_coil,'06')-3)/2) +
                      abs((locate(last_coil,'07')-3)/2) +
                      abs((locate(last_coil,'08')-3)/2) +
                      abs((locate(last_coil,'09')-3)/2) +
                      abs((locate(last_coil,'0A')-3)/2) +
                      abs((locate(last_coil,'0B')-3)/2) +
                      abs((locate(last_coil,'0C')-3)/2) +
                      abs((locate(last_coil,'0D')-3)/2) +
                      abs((locate(last_coil,'0E')-3)/2) +
                      abs((locate(last_coil,'0F')-3)/2) +
                      abs((locate(last_coil,'10')-3)/2)
                      ),0)

    FROM
        td_os
    WHERE
        last_obs_file like 'BO2$YEAR%'

    GROUP BY
        t_species
    ,   t_run
    ,   t_rear_type;
commit;
\p\g
EOFxx

ifnull:

The ifnull function specifies a value other than a null that is returned to your
application when a null is encountered. The ifnull function is specified as
follows:

ifnull(v1,v2)

If the value of the first argument is not null, ifnull returns the value of the first
argument. If the first argument evaluates to a null, ifnull returns the second
argument.

abs:

absolute value

locate:

Returns the location of the first
occurrence of c2 within c1, including
trailing blanks from c2. The location
is in the range 1 to size(c1). If c2 is
not found, the function returns
size(c1) + 1.

The last_coil field is defined as CHAR(2). So if locate finds the appropriate value in last_coil, it returns the location of the value, probably 1 or 0. If it doesn't find the value, it returns 3. Next it subtracts 3 so if the target is found, we get a negative number. If it is not found, we get zero. Then absolute value is applied giving us a positive number if the value is not found and zero if it is not found. Next we divide by 2. Perhaps this is to turn the positive number into a 1 and to leave the zero a zero.

The whole thing is summed and wrapped in an ifnull, to return zero in case nothing was found.