LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-04-2013, 03:06 PM   #1
gacanepa
Member
 
Registered: May 2012
Location: San Luis, Argentina
Distribution: Debian
Posts: 204

Rep: Reputation: 27
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!
 
Old 10-04-2013, 03:16 PM   #2
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
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.
 
Old 10-04-2013, 07:32 PM   #3
gacanepa
Member
 
Registered: May 2012
Location: San Luis, Argentina
Distribution: Debian
Posts: 204

Original Poster
Rep: Reputation: 27
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?
 
Old 10-04-2013, 08:55 PM   #4
gacanepa
Member
 
Registered: May 2012
Location: San Luis, Argentina
Distribution: Debian
Posts: 204

Original Poster
Rep: Reputation: 27
Found the solution here.
 
Old 10-04-2013, 09:02 PM   #5
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 855

Rep: Reputation: 203Reputation: 203Reputation: 203
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
 
1 members found this post helpful.
Old 10-04-2013, 09:11 PM   #6
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 4,515
Blog Entries: 6

Rep: Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426Reputation: 2426
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.

Last edited by astrogeek; 10-04-2013 at 11:38 PM. Reason: Verified ROLLUP support by Oracle
 
1 members found this post helpful.
Old 10-04-2013, 10:19 PM   #7
gacanepa
Member
 
Registered: May 2012
Location: San Luis, Argentina
Distribution: Debian
Posts: 204

Original Poster
Rep: Reputation: 27
Thank you colucix, astrogeek and AnanthaP!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
separate columns in a plain text Agustina Linux - Software 5 12-23-2011 01:30 AM
mysql sum from one of two columns secretlydead Programming 1 12-16-2009 02:11 AM
remove the extra numeric field in a text file powah Programming 13 01-08-2008 09:24 PM
.ram file shows as text/plain biosnacky Linux - Newbie 8 01-07-2007 09:21 PM
not a plain text file wazza4610 Linux - Newbie 1 11-22-2005 05:20 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 08:48 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration