How to sum the values of a specific column with the sum() function?

Asked

Viewed 788 times

0

I have the following command:

       select substr(b.chave, 28, 7) numeronf, upper(d.apelido) usuario, decode(b.loja,1,'Loja 1', 2,'CD', 3, 'Loja 2', 5,'Loja 3', 89, 'PANIF', 90, 'Restaurante' ) loja, c.nomerazao, a.data_inicial, a.data_final,
                                  to_char(trunc(( (data_final - a.data_inicial) * 86400 / 3600)),'00') ||':' || 
                                  to_char(trunc(mod( (data_final - a.data_inicial) * 86400 , 3600 ) / 60 ),'00') || ':'|| 
                                  to_char(trunc(mod ( mod ( (data_final - a.data_inicial) * 86400, 3600 ), 60 )),'00') hora 
                                  from tabela1 a, tabela2 b, tabela3 c, tabela d
                                  where a.id_chave = b.id_chave
                                  and a.usuario = d.matricula
                                  and a.usuario = $digitador
                                  and a.status = 'LN'
                                  and c.nrocgccpf = substr(b.chave, 7, 12)
                                  and c.digcgccpf = substr(b.chave, 19, 2)  
                                  and trunc(a.data_inicial) between '$data_inicial' and '$data_final'
                                  order by a.data_inicial

How does the table look:

+------------+-----------+----------+-------+----------+----------+
| Loja       | Data      |Fornecedor| NumNF | Dgitador |Hora      |
+------------+-----------+----------+-------+----------+----------+
|     01     |2014-01-02 |  Fulano  | 000000|  Fulano  |00: 06: 37|
|     01     |2014-02-07 |  Fulano  | 000000|  Fulano  |00: 06: 37|   
|     01     |2014-02-16 |  Ciclano | 000000|  Fulano  |00: 06: 37|
|     02     |2014-01-20 |  Fulano  | 000000|  Fulano  |00: 06: 37|
|     02     |2014-03-05 |  Fulano  | 000000|  Fulano  |00: 06: 37|
|     03     |2014-03-12 |  Ciclano | 000000|  Fulano  |00: 06: 37|
|     03     |2014-07-25 |  Ciclano | 000000|  Fulano  |00: 06: 37|
+------------+-----------+----------+-------+----------+----------+

How do I add the values of the hour column?

sql

1 answer

1

Add all time differences to then display the time in the desired format:

SEC_TO_TIME(SUM(data_final - a.data_inicial))

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.