LinuxQuestions.org - [SOLVED] Sum numeric columns contained in a plain text file

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - Sum numeric columns contained in a plain text file (https://www.linuxquestions.org/questions/linux-newbie-8/sum-numeric-columns-contained-in-a-plain-text-file-4175479635/)

gacanepa

10-04-2013 02:06 PM

Sum numeric columns contained in a plain text file

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

Code:

  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 :).

Code:

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!

colucix

10-04-2013 02:16 PM

A simple awk one-liner is what you need:

Code:

awk 'NR > 2 {sum1 += $1; sum2 += $2} END{printf "%'\''d %'\''d", sum1, sum2}' file

The format used in the printf statement is "%'d" with the escaped quote \' to avoid problems with the quotes that embed the awk command. Hope this helps.

gacanepa

10-04-2013 06:32 PM

Thank you colucix. Your suggestion worked pretty well.
I forgot to mention that this is Oracle Enterprise 11g.
I log in to Oracle and at the prompt I write:

Code:

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:

Code:

[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?

gacanepa

10-04-2013 07:55 PM

Found the solution here.

AnanthaP

10-04-2013 08:02 PM

Same thing occurs in most other databases (ie not just oracle) and I am not sure that it is possible to split the three portions. (SQL query statements, results and count of rows). However, the three portions are separated by a blank line. So starting after the first blank line and summing the two columns till the next blank line should give the result. Also AFAIR there is a 'nohead' or some such clause to to avoid printing the column titles.

PS: I am not sure how data with the error conditions (no records qualify, malformed query, no such database object, rights etc) will display. I mean if you plan to put it in an unattended shell script, then better mail and check the results every time.

OK

astrogeek

10-04-2013 08:11 PM

You could easily craft your SQL query to generate the sum row for you.

If your result set is as simple as your example and you are not overly concerned about performance, then a simple union would do it. I am not an Oracle guru, but I am sure it also has built-ins for doing that simply too.

ANSI SQL includes "WITH ROLLUP" as a GROUP BY modifier that will sum the grouped columns into a total row, and Oracle does support it.

gacanepa

10-04-2013 09:19 PM

Thank you colucix, astrogeek and AnanthaP!

All times are GMT -5. The time now is 11:39 PM.