Solved

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

Posted on 2010-09-16
31
591 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 49
HttpPostedFile 1 26
Call Controller Action Method from ASPX 2 14
performance query 4 22
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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