CODEWITHSUNDEEP
sqlgroup-by
FreshPro
FreshPro

Reputation: 873

From hourly data to daily data using SQL GROUP BY

I have a table like this: 

fld_id          fld_DateTime                 fld_Index
            2017-07-01 00:00:00.000             5
            2017-07-01 01:00:00.000             10
            2017-07-01 02:00:00.000             15
            2017-07-01 03:00:00.000             40
              ...........
              ...........
            2017-07-01 23:00:00.000             70
            2017-07-02 00:00:00.000             110
            2017-07-02 01:00:00.000             140
            2017-07-02 02:00:00.000             190
              ...............
              ...............
            2017-07-02 23:00:00.000             190

What I am trying to do is to group them and count sum of fld_index per day like so:

  fld_id          fld_DateTime                    SUM
                  2017-07-01                      190
                  2017-07-02                      400

Here's what I've tried:

   SELECT fld_dateTime, SUM(fld_Index) AS Sum
FROM tbl_data
WHERE
    AND fld_ConsDateTime BETWEEN '2017-07-01' AND '2017-08-02'
GROUP BY fld_dateTime

It calculates the sum but still in hourly format. How to achieve the daily format like above example?

UPDATE Monthly Part Output

2017    8   30630800.0000
2017    7   589076201.1800

Upvotes: 1

Views: 2610

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You need to extract the date. In SQL Server, you would do:

 SELECT CAST(fld_dateTime as DATE) as fld_date, SUM(fld_Index) AS Sum
 FROM tbl_data
 WHERE fld_ConsDateTime >= '2017-07-01' AND
       fld_ConsDateTime < '2017-08-03'
 GROUP BY CAST(fld_dateTime as DATE)
 ORDER BY fld_date

In MySQL, the above would work, but I would do:

 SELECT DATE(fld_dateTime) as fld_date, SUM(fld_Index) AS Sum
 FROM tbl_data
 WHERE fld_ConsDateTime >= '2017-07-01' AND
       fld_ConsDateTime < '2017-08-03'
 GROUP BY DATE(fld_dateTime)
 ORDER BY fld_date;

In both cases, you should change the WHERE clause. Your version would keep flights where the date/time is exactly midnight on 2017-08-02. Using >= and < is more accurate -- taking all date/times on one day but not the next.

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Simply cast as DATE

SELECT CAST(fld_dateTime AS DATE) AS  fld_Date, SUM(fld_Index) AS Sum
FROM tbl_data
WHERE
   fld_ConsDateTime BETWEEN '2017-07-01' AND '2017-08-02'
GROUP BY CAST(fld_dateTime AS DATE);

EDIT:

What about month? Is it the same logic?

It depends on your RDBMS, but in SQL Server you could use:

SELECT YEAR(fld_dateTime), MONTH(fld_dateTime), SUM(fld_Index) AS Sum
FROM tbl_data
GROUP BY YEAR(fld_dateTime), MONTH(fld_dateTime);

It is important to add year part to avoid grouping records from different years.

Upvotes: 5

Related Questions