How to Sum records on Field and Export to new Field

[itayzoro]

What i need is to sum all the [IN] Rows
Example:

Table 1 :
ID TIME_END IN
-------------------
1 00:01 5
2 00:31 8
3 05:01 3
4 06:01 7

so i need new table with new Field with 24 column that sum all the day from the first Fields on Table 1
and when Table 1 is update the "SUM_DAY" on Table2 is also update

Table 2 :
SUM_DAY
13 -- > its the sum of the Hour 00:00 - 00:59
0
0
0
0
3 -- > its the sum of the Hour 05:00 - 05:59
0
7 -- > its the sum of the Hour 07:00 - 07:59
till 23:59

 

[cjard]

are you asking a dataset or a database question?

 

[itayzoro]

And the Aswer is :

Database of course

 

[cjard]

You didnt post in the database section you see; it's not immediately obvious

If this was oracle, I'd do:

SELECT
  TO_CHAR(dateColumn, 'HH24') as hour,
  SUM(in) as sum_in
FROM
  table
WHERE
  dateColumn BETWEEN <date1> AND <date2>
GROUP BY
  TO_CHAR(dateColumn, 'HH24')

Givent hat you didnt mention the database in use, you can now use google to do a bit of research to how you do this in your own db, if it is not oracle

The theory is simple: reduce every date to use the hour, use the WHERE clause to exclude days you dont want, then sum the grouped hours