Solved

MS SQL 2008, SQL query: Create subgroups of 5 for 100 values

Posted on 2010-09-16
31
578 Views
Last Modified: 2012-05-10
Hi

Is possible to create a query that gets 100 rows  and create 20 subgroups with the mean value of 5  rows in each subgroup?

So 100 rows, will become 20 values for me, where each value represents the mean value of 5 rows.

Can I create a query that will do this?
0
Comment
Question by:AWestEng
  • 20
  • 11
31 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33691313
you might want to check this:
select ...

    , floor( ROW_NUMBER() OVER (ORDER BY some_order_column) / 20 ) group_value

  from yourtable 

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33691377
Not sure if I understand,
Table Name> TestData
Column that I what to get the mean value of> ResonanceFrequency
So 100 rows of ResonanceFrequency will become 20 rows where each row represents the mean value of 5 ResonanceFrequency
 


 
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33691419
ok, this should do:
select floor(rn/5) group_ky

   , avg(ResonanceFrequency)

from (

  select ResonanceFrequency

    , ROW_NUMBER() OVER (ORDER BY some_order_column) rn

  from yourtable 

  ) 

group by floor(rn/5) 

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33691646
Can't get it to work
Ok, this is the table
 
 

USE [TestDB]

GO



/****** Object:  Table [dbo].[TestData]    Script Date: 09/16/2010 15:27:25 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[TestData](

	[ID] [bigint] IDENTITY(1,1) NOT NULL,

	[SerialNumber] [nchar](15) NULL,

	[TimeStamp] [datetime] NOT NULL,

	[TypeID] [tinyint] NOT NULL,

	[ResonanceFrequency] [float] NOT NULL,

 CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO



ALTER TABLE [dbo].[TestData] ADD  CONSTRAINT [DF_TestData_TimeStamp]  DEFAULT (getdate()) FOR [TimeStamp]

GO

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33691671
can you please show sample data from the table, with the required output?
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33691762
A row look like this
 
Some data for type 10
SerialNumber>. T12345678
TimeStamp> 2010-08-30 17:15:59.710
TypeID->10
ResonanceFrequency-> 757
SerialNumber>. T12344673
TimeStamp> 2010-08-30 17:18:59.710
TypeID->10
ResonanceFrequency-> 737  
 SerialNumber>. T12666673
TimeStamp> 2010-08-30 17:34:59.710
TypeID->10
ResonanceFrequency-> 739

SerialNumber>. T12665555
TimeStamp> 2010-08-30 17:34:59.710
TypeID->10
ResonanceFrequency-> 779  
  SerialNumber>. T1455555
TimeStamp> 2010-08-30 17:34:59.710
TypeID->10
ResonanceFrequency-> 759  
SQL query for type 10
Output
TypeID = 10
ResonanceFrequency  = (757 + 737  + 739 + 779  + 759 ) / 5
SQL query for type 12
Output
TypeID->12
ResonanceFrequency  = (567+ 578  + 545 + 598  + 456 ) / 5
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33691930
let's see:
select TypeID

   , floor(rn/5) group_ky

   , avg(ResonanceFrequency)

from (

  select TypeID, ResonanceFrequency

    , ROW_NUMBER() OVER (partition by TypeID ORDER BY TimeStamp) rn

  from yourtable 

  ) 

group by TypeID, floor(rn/5) 

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33698906

I'll this err mess.
Incorrect syntax near the keyword 'group'
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33699138
sorry ... syntax "detail". the subquery needs an alias too,...
select TypeID

   , floor(rn/5) group_ky

   , avg(ResonanceFrequency)

from (

  select TypeID, ResonanceFrequency

    , ROW_NUMBER() OVER (partition by TypeID ORDER BY TimeStamp) rn

  from yourtable 

  ) sq

group by TypeID, floor(rn/5) 

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33699248
no errors,, thx man,
 Can you explain the query abit for me :)
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33699259
Some data from the query
ex.bmp
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33699308
if you run just the inner part of the query, you might see what happens with the outside:

select TypeID, ResonanceFrequency

    , ROW_NUMBER() OVER (partition by TypeID ORDER BY TimeStamp) rn

    , floor( ROW_NUMBER() OVER (partition by TypeID ORDER BY TimeStamp) / 5 ) group_key 

  from yourtable 

order by TypeID, timestamp

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33700151
if the grouping is started from the bottom to top it would be good, because this is going to be used in a graph with 20 point, and when new values is rolling in I only what the last group to change. So you will see 20 point in the graph where the last point represent the last "5" inserted records .
But now is sorts the TimeSamp from the oldest date to the newest, is there a way to flip that, but still going from the bottom to the top when grouping? and the also get the top 20 groupings?
 
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33700207
If I run this query is look perfect Now I only need to group them toghter in this order to.

select [TypeID], ResonanceFrequency, TimeStamp

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

    , floor( ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) / 5 ) group_key 

  from [Cirrus].[dbo].[TestData] 

order by TimeStamp DESC, [TypeID]

Open in new window

e.bmp
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33700356
so you want this:
select TypeID

   , floor(rn/5) group_ky

   , avg(ResonanceFrequency)

from (

  select TypeID, ResonanceFrequency

    , ROW_NUMBER() OVER (partition by TypeID ORDER BY TimeStamp DESC) rn

  from yourtable 

  ) sq

group by TypeID, floor(rn/5) 

order by TypeID, floor(rn/5) 

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:AWestEng
ID: 33700405
If I run this code it will look like this.. see image
and as you can see it should not be type 8 it should have been 10, because 10 is the lasted produced type as you see in the image in the previous post

SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

  FROM [Cirrus].[dbo].[TestData] 

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

e.bmp
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33700779
if you want only the latest produced typeid:
SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

  FROM [Cirrus].[dbo].[TestData] 

  WHERE TypeID = ( Select top 1 TypeID FROM [Cirrus].[dbo].[TestData] order by TimeStamp desc ) 

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722722
thx, but with that query I ony get 10's back if there are only 5 10's l need 15 9's or whatever has been produced before the 10's, any tips ?
 
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33722780
ok, another try:
SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT TOP 20 [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

  FROM [Cirrus].[dbo].[TestData] 

  WHERE [TypeID] IN ( SELECT TOP 20 TypeID FROM [Cirrus].[dbo].[TestData] ORDER BY TimeStamp DESC )

  ORDER BY TimeStamp DESC

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722840
If I just run the inner query
it will return this.
 

select [TypeID], ResonanceFrequency, TimeStamp

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

    , floor( ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) / 5 ) group_key 

  from [Cirrus].[dbo].[TestData] 

order by TimeStamp DESC, [TypeID]

Open in new window

1.bmp
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722847
and if I run the new query it will return this

SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT TOP 20 [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

  FROM [Cirrus].[dbo].[TestData] 

  WHERE [TypeID] IN ( SELECT TOP 20 TypeID FROM [Cirrus].[dbo].[TestData] ORDER BY TimeStamp DESC )

  ORDER BY TimeStamp DESC

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

2.bmp
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722864
So only 5 results back, and in the second post above this as I marked the result should be 725,8 but in the picture above its 733.25
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722926
ok, my bad, it seams that it will sum the vaules correct
this will be 726
 

select [TypeID], ResonanceFrequency, TimeStamp

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp desc ) rn

    , floor( ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp desc) / 5 ) group_key 

  from [Cirrus].[dbo].[TestData] 

order by TimeStamp DESC, [TypeID]

Open in new window

11.bmp
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722936
and if I check the new one
I have 726 in the second post, so that is correct, but why do I ony get 5 posts back?

SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT TOP 20 [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp) rn

  FROM [Cirrus].[dbo].[TestData] 

  WHERE [TypeID] IN ( SELECT TOP 20 TypeID FROM [Cirrus].[dbo].[TestData] ORDER BY TimeStamp DESC )

  ORDER BY TimeStamp DESC

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

22.bmp
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33722993
ok  I found it, it shoud be TOP 100 in the inner query
SELECT TOP 20 [TypeID]

   , FLOOR(rn / 5) group_ky

   , AVG([ResonanceFrequency]) ResonanceFrequency

FROM (

SELECT TOP 100 [TypeID], [ResonanceFrequency]

    , ROW_NUMBER() OVER (partition by [TypeID] ORDER BY TimeStamp DESC) rn

  FROM [Cirrus].[dbo].[TestData] 

  WHERE [TypeID] IN ( SELECT TOP 100 [TypeID] FROM [Cirrus].[dbo].[TestData] ORDER BY TimeStamp DESC )

  ORDER BY TimeStamp DESC

  ) sq  

GROUP BY [TypeID], FLOOR(rn/5)

ORDER BY [TypeID], floor(rn/5)

Open in new window

0
 
LVL 1

Author Comment

by:AWestEng
ID: 33723005
Thx man, you are the best!!! WORKS PERFECT!! :)
0
 
LVL 1

Author Closing Comment

by:AWestEng
ID: 33723013
Perfect!!!! ::)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33723079
Glad I could help
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33724672
angelIII: One question, is it possible to get the number of items that each group consists of, beacse the last group can be all from 1-5 and I like to know that.
Or is it possible to only get goups of five and skip the last 1 to 4 items until they becoms 5?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33724732
add count(*) in the main select list ...
0
 
LVL 1

Author Comment

by:AWestEng
ID: 33724754
hehe.. Thx man,, :) You are to fast
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now