Solved

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

Posted on 2010-09-16
31
567 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

8 Experts available now in Live!

Get 1:1 Help Now