CODEWITHSUNDEEP
oracle-databaseselectgroup-bytimestamp
Veloster3k
Veloster3k

Reputation: 11

Select SUM with GROUP BY by day interval

I have this table in Oracle Database:

       date       |   column1   |  column2
01/05/2020 00:00  |     50      |     20
01/05/2020 00:15  |     60      |     30
01/05/2020 00:30  |     70      |     40
       ...        |             |
01/05/2020 23:45  |     80      |     50
02/05/2020 00:00  |     100     |     40
02/05/2020 00:15  |     110     |     35

And I have this SELECT script:

SELECT
    period,
    liquid    
FROM
    (
        SELECT
            ( nvl(SUM(m.column1), 0) - nvl(SUM(m.column2), 0) ) liquid,            
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY') period
        FROM
            table m
        WHERE
            m.id_register IN (id_register)
            AND m.date BETWEEN TO_DATE('01/05/2020 00:15:00', 'dd/mm/yyyy hh24:mi:ss') AND TO_DATE('01/06/2020 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
        GROUP BY
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY')
    ) vrdm

I need to sum (difference column1 and column2) all day registers in interval between 00:15 and 00:00 (next day) per day. How to make it?

The result that I need is like this:

  period   |  liquid
01/05/2020 |   3000     -> SUM(all differences between column1 and column2 between 01/05 00:15 and 02/05 00:00)
02/05/2020 |   4000     -> SUM(all differences between column1 and column2 between 02/05 00:15 and 03/05 00:00)
03/05/2020 |   3500     -> SUM(all differences between column1 and column2 between 03/05 00:15 and 04/05 00:00)

Upvotes: 1

Views: 37

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Just check time part in where clause:

SELECT to_char(trunc(m.dt), 'dd/MM/YYYY') period,
       nvl(SUM(m.column1), 0) - nvl(SUM(m.column2), 0) liquid
  FROM m
  WHERE to_char(m.dt, 'hh24:mi') >= '00:15' 
  GROUP BY trunc(m.dt)

dbfiddle

Upvotes: 1

Related Questions