tsql group by clause with sum and multiple columns

Question

I'm trying to correctly write TSQL to create a new table containing the following old and newly derived columns:

Date, 10minTime, Code, Price, SumItems.

The new table should include two derived columns; 10minTime comprising only 10min time periods (eg 10:40, 10:30, 10:20), and the SumItems comprising the sum of items within each 10min period. The other columns should be unchanged.

The original data is something like this:

    Date        Time       Code Price   Items
    2012-05-10  10:43:00    a   1351.9  2
    2012-05-10  10:42:00    a   1351.7  5
    2012-05-10  10:41:00    a   1351.3  5
    2012-05-10  10:40:00    a   1351.5  8
    2012-05-10  10:39:00    a   1351.8  3
    2012-05-10  10:38:00    a   1351.9  5
    2012-05-10  10:37:00    a   1351.5  6
    2012-05-10  10:35:00    a   1352.2  2
    2012-05-10  10:34:00    a   1352.0  3
    2012-05-10  10:32:00    a   1352.3  6

This is what i have so far, but it has errors. If i remove [Price] then it runs but produces the wrong result.

SELECT TOP 1000 [date]
      ,min([time]) as 10minTime
      ,[price]
      ,sum([Items]) as SumItems
  FROM [MarketData].[dbo].[MyData]
  group by [Date],DATEPART(hour, [Time]),(DATEPART(minute, [Time]))%10
  order by [date] desc, 10minTime desc

Thank you for any advice / help!

possible duplicate of [How to group time by hour or by 10 minutes](http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — GSerg, Jun 29 '12 at 16:54
GSerg - i saw that question but want to adapt to include multiple columns — Yugmorf, Jun 29 '12 at 17:01
LittleBoddyTables - New table should have price that = the minimum time within the 10min interval time stamp — Yugmorf, Jun 29 '12 at 17:03
LittleBoddyTables - To be more clear. The new table should take the price from the row that matches the row containing the minimum time (within the 10min interval time stamp) — Yugmorf, Jun 29 '12 at 17:11

Raphaël Althaus · Accepted Answer · 2012-06-29T17:26:12.190

1

SELECT TOP 1000 
     m1.date
      ,min(m1.time) as 10minTime
      ,(Select price from dbo.MyData m2
        where m2.Date = m1.Date
        and DATEPART(hour, m2.Time) = DATEPART(hours, m1.Time)
        and DATEPART(minute, m2.Time) = DATEPART(minute, m2.Time) %10)

      ,sum(Items) as SumItems
  FROM dbo.MyData m1
  group by [Date],DATEPART(hour, [Time]),(DATEPART(minute, Time))%10
  order by [date] desc, 10minTime desc

edited Jun 29 '12 at 17:26

answered Jun 29 '12 at 16:54

Raphaël Althaus

59,727
6
96
122

The new table should take the price from the row that matches the row containing the minimum time (within the 10min interval time stamp). That requirement would seem not to fit any of the above functions. – Yugmorf Jun 29 '12 at 17:18
@user1225208 ok, so the edited version is maybe better, but untested. – Raphaël Althaus Jun 29 '12 at 17:28
Great! i'll go with that. Untested it might be, but understandable it is. I can modify if needed. Thank you – Yugmorf Jun 29 '12 at 17:43

tsql group by clause with sum and multiple columns

1 Answers1