shell script which will interact with the database

Question

Have been writing the shell script such as :

#! /bin/bash
`sqlplus -s <username>/<passwd>@dbname`    
set echo on  
set pagesize 0  
set verify off  
set lines 32000  
set trimspool on  
set feedback off  
`SELECT tr.number, count(*) as total  
              FROM <dbname>.<tablename1> tr  
              LEFT JOIN <tablename2> t2 ON t2.id2 = tr.id1  
              LEFT JOIN <tablename3> t3 ON t3.id2 = tr.id1  
              LEFT JOIN <tablename4> t4 ON t4.id2 = tr.id1  
              WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440  
               AND tr.TIMESTAMP <= SYSDATE - 15 / 1440  
              and (t2.number like '2____' OR t2.number like '3____' OR t2.number like '99____' )  
AND tr.id = 703 and t2.v1 = 100  
          group by (tr.number);`  
exit;  
EOF

Currently my output is like ::

 tr.number                    count(*)  
 27684                        76        
 27050                         9        
 37744                         7        
 997717                       11        
 997797                        8        
 37224                         3       

I want to group the count of numbers 2_, 3__, 99__ such as

tr.number  count(*)  
 2____      76+9  
 3____      7+3  
 99_____    11+8  

the output should be ONLY in 3 rows with starting digits as 2,3 and 99 in three rows as shown above.

_ represents 4 places.

Please advise.

P.S. Could you please advise why is it essential to put SET operators and their brief description.

Thanks !

Answer 1

You can get the first one or two characters of tr.number and then group on it to get the expected results.

Since you haven't provided the table creation scripts, I've created a demo table and its data to illustrate the point.

create table tr(
   id number,
   total_records number);

insert into tr values (200123,50);
insert into tr values (20034, 25);
insert into tr values (30034, 15);
insert into tr values (300567, 100);
insert into tr values (990034, 75);
insert into tr values (990034, 80);
insert into tr values (990034, 456);
commit;


SELECT   starts_with, SUM (total_records) total_records
    FROM (SELECT ID,
                 (CASE WHEN ID LIKE '2%'  THEN '2____'
                       WHEN ID LIKE '3%'  THEN '3____'
                       WHEN ID LIKE '99%' THEN '99____'
                  END
                 ) starts_with,
                 total_records
            FROM tr
            where ( id like '2%' or id like '3%' or id like '99%'))
GROUP BY starts_with;

STARTS TOTAL_RECORDS
------ -------------
2____             75
3____            115
99____           611

Answer 2

You can see what the SET commands do in the manual: http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm

Answer 3

Thanks Rajesh,

Now, i have to send the output via email to a particular email-address from script if the total_records gets equal to 0. That email should have subject and body as well.