Solved

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

Posted on 2010-09-16
31
618 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 11
31 Comments
 
LVL 143

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 143

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 143

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 143

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 143

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 143

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 143

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 143

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 143

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 143

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 143

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

689 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