Rafa Ayadi Rafa Ayadi - 6 months ago 9
SQL Question

How to sum a group of rows in SQL?

I have a table looking like this

ID Customer TimeStamp Occ
14488 5130 2016-05-11 11:16:52.740 3
114304 5130 2016-05-11 11:01:51.263 3
114156 5130 2016-05-11 10:46:49.943 3
113971 5130 2016-05-11 10:31:49.857 5
113790 5130 2016-05-11 10:16:49.510 6
113605 5130 2016-05-11 10:01:48.643 1
113419 5130 2016-05-11 09:46:46.843 3
113234 5130 2016-05-11 09:31:46.643 2
113052 5130 2016-05-11 09:16:45.403 2
112867 5130 2016-05-11 09:01:44.193 9
112681 5130 2016-05-11 08:46:42.220 8
112493 5130 2016-05-11 08:31:38.327 3
112305 5130 2016-05-11 08:16:37.220 2
112122 5130 2016-05-11 08:01:37.147 8
111936 5130 2016-05-11 07:46:35.933 5
111747 5130 2016-05-11 07:31:34.457 5


What I want to do is to merge the rows of this table, four by four, and sum the occurence of that. It would be great also if I could print the earliest and latest timestamps of the merged 4 rows. The output would be like this :

ID Customer Last TimeStamp EarliestTimeStamp Sum Occ
1 5130 2016-05-11 11:16:52.740 2016-05-11 10:31:49.857 14 //Sum of the first 4 rows (3+3+3+5)
2 5130 2016-05-11 10:16:49.510 2016-05-11 09:31:46.643 12 //Sum of the first 4 rows (6+1+3+2)


Is there any way I could do that with SQL?

Answer

The query

SELECT ID, Customer, TimeStamp, Occ,
  (SELECT 1+FLOOR(COUNT(1)/4) FROM table1 it
    WHERE ot.Customer=it.Customer AND it.TimeStamp<ot.TimeStamp) AS seq
 FROM table1 as ot

Will add a sequence number - the first 4 are labelled 1, the next 4 labeled 2 etc. Note that the function FLOOR truncates the float to an integer - it might be called INT in your database.

 ID   Customer       TimeStamp          Occ   seq
14488   5130    2016-05-11 11:16:52.740 3     1
114304  5130    2016-05-11 11:01:51.263 3     1
114156  5130    2016-05-11 10:46:49.943 3     1
113971  5130    2016-05-11 10:31:49.857 5     1
113790  5130    2016-05-11 10:16:49.510 6     2
113605  5130    2016-05-11 10:01:48.643 1     2
113419  5130    2016-05-11 09:46:46.843 3     2
113234  5130    2016-05-11 09:31:46.643 2     2

You can then get the max and min and sum relatively easily:

SELECT seq, Customer, min(TimeStamp), max(TimeStamp), SUM(occ)
FROM (
  SELECT ID, Customer, TimeStamp, Occ,
    (SELECT 1+FLOOR(COUNT(1)/4) FROM table1 it WHERE ot.Customer=it.Customer and it.TimeStamp<ot.TimeStamp) AS seq
   FROM table1 as ot
) AS dt