Sum numeric columns contained in a plain text file

Shell Programming and Scripting
1

Hi everyone,
Here are the contents of a plain text file created by a SQL query:

   SUM(T.TRNQTY)  COUNT(D.TRNSEQ)
---------------- ----------------
            1380               46
            1393               59
            2680              134
             740               37
             620               31
            1470               42
            3536              148
            5634              188
            2564              129

How can I sum the numeric columns to get the following result? No need to solve the problem for me. Just point me in the right direction and I'll be more than thankful :).

20,017           814

I'm in Red Hat Enterprise Linux Server release 5.7 (Tikanga) and using GNU bash, version 3.2.25(1)-release (i386-redhat-linux-gnu).
Thanks in advance!

2

Try:

awk 'NR>2{a+=$1;b+=$2}END{print a"\t"b}' file
1 Like
3

Why not use the resp. SQL- command?

4

bartus11's suggestion worked fairly well.
I forgot to mention that this is Oracle Enterprise 11g.
I log in to Oracle and at the prompt I write:

sql> spool my_query.txt;
sql> [my query here];
sql> spool off;
sql> exit

I then return to my command prompt and when I open the file my_query.txt, besides the result of the query I also see the following:

[Whole SQL query here]
[Result of query here]
[Number of rows returned here]

Obviously I only want to save the query results... I still need to find a way to get rid of the other stuff (the text of the query and the # of rows returned by it).
Any ideas?

5

There might be a SET option for it.

EDIT:
Try

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 0
1 Like
6

Thank you very much!
I was almost there and couldn't understand why the text of the query was still being saved in the text file, until I read that the SET options are only good if you use it in a sql script (separate file) and then source to in the bash script, like so:
File query.sql:

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL myfile.txt
SELECT field1, field2 FROM mytable;
SPOOL OFF
exit

Then in the bash script:

#!/bin/bash
sql @query sql

It worked like a charm!

1 Like