QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

QUERY

Author

Topic

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 05:36:30

Hi
I have table data like this
ID COL1 COL2 RANK
1 2 0.93 1
2 2 1.40 2
3 2 1.87 3
4 3 0.67 1
5 3 1.00 2
6 3 1.33 3
7 4 0.22 1
8 4 0.33 2
9 4 0.44 3

Avg are should be colored sequence...

I want the AVG data like this

COL1 AVG
2 0.606666
3 0.910000
4 1.213333

-------------------------
Your time is a valuable resource.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-08 : 05:42:18

You mean you want the average PER RANK?

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 05:43:32

yes but i want the distinct value of col1 values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-08 : 05:45:03

[code]DECLARE @Sample TABLE
(
ID INT,
Col1 INT,
Col2 MONEY,
Rank INT
)

INSERT @Sample
SELECT 1, 2, 0.93, 1 UNION ALL
SELECT 2, 2, 1.40, 2 UNION ALL
SELECT 3, 2, 1.87, 3 UNION ALL
SELECT 4, 3, 0.67, 1 UNION ALL
SELECT 5, 3, 1.00, 2 UNION ALL
SELECT 6, 3, 1.33, 3 UNION ALL
SELECT 7, 4, 0.22, 1 UNION ALL
SELECT 8, 4, 0.33, 2 UNION ALL
SELECT 9, 4, 0.44, 3

SELECT *
FROM @Sample

SELECT Rank,
AVG(Col2) AS theAverage
FROM @Sample
GROUP BY Rank[/code]

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 05:50:50

hi

thanks but i want the distinct value of COL1 instead of Rank.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 05:51:43

like this..

COL1 AVG
2 0.606666
3 0.910000
4 1.213333

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-08 : 06:04:38

Hi, peso...can you make make row_number increase to 1,2,3

table01
col1
1
2
3
4
5
6
7
8
9
10

require result
col1 (no column name)
1 1
2 2
3 3
4 1
5 2
6 3
7 1
8 2
9 3
10 1

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 06:09:57

Hi

already row_number is there column is ID .
but the COL1 data are
2
2
2
3
3
3
4
4
4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 06:13:40

quote:
Originally posted by rajdaksha

like this..

COL1 AVG
2 0.606666
3 0.910000
4 1.213333

How does
COL1 = 2 associate with 0.606666 ?
COL1 = 3 associate with 0.910000 ?
etc

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 06:30:15

quote:
Originally posted by rajdaksha

like this..

COL1 AVG
2 0.606666
3 0.910000
4 1.213333

Not sure if this works for all set of data


DECLARE	@Sample TABLE
	(
		ID INT,
		Col1 INT,
		Col2 MONEY,
		Rank INT
	)

INSERT	@Sample
SELECT	1, 2, 0.93, 1 UNION ALL
SELECT	2, 2, 1.40, 2 UNION ALL
SELECT	3, 2, 1.87, 3 UNION ALL
SELECT	4, 3, 0.67, 1 UNION ALL
SELECT	5, 3, 1.00, 2 UNION ALL
SELECT	6, 3, 1.33, 3 UNION ALL
SELECT	7, 4, 0.22, 1 UNION ALL
SELECT	8, 4, 0.33, 2 UNION ALL
SELECT	9, 4, 0.44, 3

select distinct t1.col1,t2.theAverage from
(
	select (id-rank)/3+1 as new_rank,* from @sample
) as t1 inner join
(
SELECT	Rank,
		AVG(Col2) AS theAverage
FROM		@Sample 
GROUP BY	Rank
) as t2 on t1.new_rank=t2.rank

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 06:43:55

hi

This is working fine thanks..
but the maximum col1 distinct data are 5.

can you explain pls what u have done ...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-08 : 08:05:46

quote:

select distinct t1.col1,t2.theAverage from
(
select (id-rank)/3+1 as new_rank,* from @sample
) as t1 inner join
(
SELECT Rank,
AVG(Col2) AS theAverage
FROM @Sample
GROUP BY Rank
) as t2 on t1.new_rank=t2.rank
[/code]

Madhivanan

Failing to plan is Planning to fail

i have done like this i hope this works for all set of data....

SELECT DISTINCT T1.COL1,T2.AVERAGE FROM
(
SELECT DENSE_RANK() OVER (ORDER BY COL1)AS NEW_RANK ,* FROM @Sample
)AS T1 INNER JOIN
(
SELECT RANK,
AVG(COL2) AS AVERAGE
FROM @Sample
GROUP BY RANK
) AS T2 ON T1.NEW_RANK=T2.RANK

-------------------------
Your time is a valuable resource.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 09:45:36

quote:
Originally posted by rajdaksha

quote:

select distinct t1.col1,t2.theAverage from
(
select (id-rank)/3+1 as new_rank,* from @sample
) as t1 inner join
(
SELECT Rank,
AVG(Col2) AS theAverage
FROM @Sample
GROUP BY Rank
) as t2 on t1.new_rank=t2.rank
[/code]

Madhivanan

Failing to plan is Planning to fail

i have done like this i hope this works for all set of data....

SELECT DISTINCT T1.COL1,T2.AVERAGE FROM
(
SELECT DENSE_RANK() OVER (ORDER BY COL1)AS NEW_RANK ,* FROM @Sample
)AS T1 INNER JOIN
(
SELECT RANK,
AVG(COL2) AS AVERAGE
FROM @Sample
GROUP BY RANK
) AS T2 ON T1.NEW_RANK=T2.RANK

-------------------------
Your time is a valuable resource.

You are welcome

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 09:46:13

quote:
Originally posted by waterduck

Hi, peso...can you make make row_number increase to 1,2,3

table01
col1
1
2
3
4
5
6
7
8
9
10

require result
col1 (no column name)
1 1
2 2
3 3
4 1
5 2
6 3
7 1
8 2
9 3
10 1

declare @t table(n int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10

select n,case when n%3=0 then 3 else n%3 end as seqno from @t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources