SQL Grouping SUM

Applications Programming
1

Hello people, I'm scratching my head to find a solution to this query.

I have a simple SQL table:

data        | tot
==================
111201/0000 | 3
111201/0001 | 5 
111201/0002 | 7 
111201/0003 | 2 
.....
111201/0059 | 1 
111201/0100 | 5 
111201/0101 | 8 
111201/0102 | 6
111201/0103 | 2
....

What, what I need to do is make a 'tot' SUM based on data column. 0000, 0001, 0002, is the time (00:00, 00:01, 00:02).

So if I make this select::

SELECT SUM(`tot`) FROM `data` WHERE `data` like '%/00%'

The output will be the 'tot' SUM from the first hour (0000 to 0059), so for the next hour I should make another select like:

SELECT SUM(`tot`) FROM `data` WHERE `data` like '%/01%'

Is it possible to make only one select to show me the SUM of each hours?

Thanks!

2

if the result of you SQL extract is stored in file mySQLextractFile.txt:

nawk -F'[/|]' '/^[0-9]/{a[$1"/" int($2/100)]+=$NF}END{for(i in a) print i,a}' OFS='|' mySQLextractFile.txt
3

Nope, I'll elaborate the result after the select. I need directly the SUM for each hour from output SQL select!
Maybe using grouping (and SUM) by partial, but I don't know how to implement yet!

---------- Post updated at 11:48 AM ---------- Previous update was at 11:40 AM ----------

Well, I found myself a solution, it was so simple!!! :slight_smile:

SELECT data, SUM(`tot`) as SUM, COUNT(tot) As HOUR FROM `data` GROUP BY MID(data, 8,2)

Maybe can help some other user! :wink:

4

-- deleted --

(Ooops cross post)

5

Simple text, but I've already found a solution :wink:

6

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:

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

7

@Lord Spectre
I had this (in Oracle):

SQL> select substr(data,0,9), sum(tot) from mytable group by substr(data,0,9);
SUBSTR(DA   SUM(TOT)
--------- ----------
111201/01          5
111201/00         15

(I guess your SQL is MySQL?)

8

Thanks both! I'll analyze your select since my one have some problem when day change (the first 6 chars)! :slight_smile:

...and yes, I'm using MySQL and bash script, but as rbatt1 said, I would save processing time to get the database to perform all the work! Database is not on the same machine, so I save also CPU usage! :wink: