SQL Grouping SUM Post: 302587288

Sponsored Content

Top Forums Programming SQL Grouping SUM Post 302587288 by rbatte1 on Wednesday 4th of January 2012 11:51:24 AM

01-04-2012

Moderator

Not sure if this is strictly a unix issue, but in any case,......

What database are you using? There is likely a date/time function we can call in your query to produce your results straight off the bat.

Your logic of using a where clause based on being like '%/00', thrn '%/01' etc. could generate you spurious values.

Something like:

Code:

SELECT to_char(to_date(data,'yymmdd/hhmi'),'yymmdd/hh') timestamp , SUM(tot) sumtot
FROM table
GROUP BY timestamp
ORDER BY timestamp ;

Might get you started and perhaps use of a trimming function to ignore the minutes value will get the actul result you want straight from the SQL query, but like I say, it depends on the database server you are using.

Wholly untested bit of theory, but I hope that this helps a bit. If you respond with the database software, you may get a complete response. In my opinion you would save processing time to get the database to perform the work within the initial query rather than get the data to a file then post-process it to create the output you want. You can use the to_char function of many databases to give you date/time output in the format you want too.

Robin
Liverpool/Blackburn,
UK

rbatte1

View Public Profile for rbatte1

Visit rbatte1's homepage!

Find all posts by rbatte1

8 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

grouping and sorting??

how would I write a command line that creates a new file named stuff.txt in the current working directory which contains the number of directories in the current working directory, followed by the number of empty files in the current working directory, followed by the name of the working directory?

2. Shell Programming and Scripting

Print sum and relative value of the sum

Hi i data looks like this: student 1 Subject1 45 55 Subject2 44 55 Subject3 33 44 // student 2 Subject1 45 55 Subject2 44 55 Subject3 33 44 i would like to sum $2, $3 (marks) and divide each entry in $2 and $3 with their respective sums and print for each student as $4 and...

3. Shell Programming and Scripting

PERL : SQL array bind issue while grouping

Hi, I have an array of strings. Each string has 4 comma separated values. I am binding this array to a SQL where I am required to do an INSERT after grouping. The binding is done as : $insertADWSth->bind_param_array(1,A_CONSTANT_STRING);...

4. UNIX for Dummies Questions & Answers

Grouping in grep

How do you do grouping in grep? Here's how I tried it at first: egrep 'qualit(y|ies)' /usr/share/dict/words -bash: syntax error near unexpected token `(' I'm using GNUgrep, and I found this on their site. grep regular expression syntax So I tried this: egrep 'qualit$y\|ies$'...

5. Shell Programming and Scripting

Grouping

Hi all, I am using following command: perl program.pl input.txt output.txt CUTOFF 3 > groups_3.txt containing program.pl, two files (input.txt, output.txt) and getting output in groups_3.txt: But, I wish to have 30 files corresponding to each CUTOFF ranging from 0 to 30 using the same...

6. Shell Programming and Scripting

Grouping output with sum

Hi Gurus, I would like to do in a script something that is really easy in sql. Basically, I have a file with a fomat like the following: name1_test 501 name 1 33 510 test2 900 name1_test 300 So, I would like to group them as...

7. Shell Programming and Scripting

Name grouping

awk 'FNR==NR {a; next} $NF in a' genes.txt refseq_exons.txt > output.txt I can not figure out how to group the same name in $4 together. Basically, all the SKI together in separate rows and all the TGFB2. Thank you :). chr1 2160133 2161174 SKI chr1 218518675 218520389 TGFB2...

8. Shell Programming and Scripting

Grouping and counting

Hello, I would like to group/sort a file of records by a particular field and then count how many records belong in that grouping. For example say I have the following data: 1234|"ZZZ"|"Date"|"1"|"Y"|"ABC"|""|AA ABCD|"ZZZ"|"Date"|"1"|"Y"|"ABC"|""|AA EFGH|"ZZZ"|"Date"|"1"|"Y"|"ABC"|""|BB...

LEARN ABOUT REDHAT

execute

EXECUTE(7)							   SQL Commands 							EXECUTE(7)

NAME

       EXECUTE - execute a prepared query

SYNOPSIS

	  EXECUTE plan_name [ (parameter [, ...] ) ]

   INPUTS
       plan_name
	      The name of the prepared query to execute.

       parameter
	      The  actual  value  of a parameter to the prepared query.  This must be an expression yielding a value of a type compatible with the
	      data-type specified for this parameter position in the PREPARE statement that created the prepared query.

DESCRIPTION

       EXECUTE is used to execute a previously prepared query. Since prepared queries only exist for the duration of a session, the prepared query
       must have been created by a PREPARE statement executed earlier in the current session.

       If  the	PREPARE  statement  that created the query specified some parameters, a compatible set of parameters must be passed to the EXECUTE
       statement, or else an error is raised. Note that (unlike functions) prepared queries are not overloaded based on  the  type  or	number	of
       their parameters: the name of a prepared query must be unique within a database session.

       For more information on the creation and usage of prepared queries, see PREPARE [prepare(7)].

COMPATIBILITY

   SQL92
       SQL92  includes	an EXECUTE statement, but it is only for use in embedded SQL clients. The EXECUTE statement implemented by PostgreSQL also
       uses a somewhat different syntax.

SQL - Language Statements					    2002-11-22								EXECUTE(7)