CODEWITHSUNDEEP
t-sqlgroup-by
Yugmorf
Yugmorf

Reputation: 340

tsql group by clause with sum and multiple columns

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!

Upvotes: 0

Views: 2989

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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

Upvotes: 1

Related Questions