September 18, 2002 at 1:41 pm
Hi. I need to select rows in groups. In other words if a query returns a rowcount of 70 I need to select those 70 rows in groups of 10 so I can AVG them. So I need to select the first 10 and avg, then the second 10 and avg, and so on. I can do this a kloodgy way by selecting the top 10, creating a new view, going back and deleteing the top to, selecting the top ten . . and then unioning all my view. I am hoping there is a better way. Thanks for any help
September 18, 2002 at 2:39 pm
Are they ordered?
Steve Jones
September 18, 2002 at 2:57 pm
Yes they are order by [ID].
September 18, 2002 at 4:52 pm
This is also a kludge, but at least the original table stays intacked and no views.
This example avg's every two royalties together in the pubs.dbo.title table.
use pubs
create table #temp (id int identity(1,1), royalty int)
insert into #temp select royalty from titles
go
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 18 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 16 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 14 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 12 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 10 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 8 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 4 royalty,id from #temp order by id desc) a
order by id asc) b)
union all
(select avg(royalty) as avg from (select top 2 royalty,id from (select top 2 royalty,id from #temp order by id desc) a
order by id asc) b)
drop #temp
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 9:33 am
Greg that is an interesting way to do it. It would definatley work. The way I have decided to do it is to get a rowcount and do some math along the way. This will be in a string that is executed as a number of sub-selects in the end. Thanks for the replies.
September 24, 2002 at 10:05 am
This wil work fine,
select a.nrow, avg(a.royalty) from
(select (select count(*)+1 from titles as aa where aa.id < xx.id )/10 as nrow, * from titles as xx) as a
group by a.nrow
if your table is quite small
quote:
Greg that is an interesting way to do it. It would definatley work. The way I have decided to do it is to get a rowcount and do some math along the way. This will be in a string that is executed as a number of sub-selects in the end. Thanks for the replies.
September 25, 2002 at 4:43 am
Jorge's solution is great and should meet most situations. But as an alternative to use in case of performance you could create a temp table like so.
CREATE TABLE #tmp (
[idx] [int] IDENTITY(1,1) NOT NULL,
[royalty] [int] NOT NULL
)
Then run your query to get your records and insert the royalty amounts into the temp able.
Then do similar to what Jorge did.
select (a.idx/10) as tenspot, avg(a.royalty) from
#tmp AS a
group by a.idx/10
Just an alternative.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply