• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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

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
AWestEng
Asked:
AWestEng
  • 20
  • 11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show sample data from the table, with the required output?
0
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:

I'll this err mess.
Incorrect syntax near the keyword 'group'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
no errors,, thx man,
 Can you explain the query abit for me :)
0
 
AWestEngAuthor Commented:
Some data from the query
ex.bmp
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
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
 
AWestEngAuthor Commented:
Thx man, you are the best!!! WORKS PERFECT!! :)
0
 
AWestEngAuthor Commented:
Perfect!!!! ::)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Glad I could help
0
 
AWestEngAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
add count(*) in the main select list ...
0
 
AWestEngAuthor Commented:
hehe.. Thx man,, :) You are to fast
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 20
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now