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

General SQL Server Forums

New to SQL Server Programming

Adding Columns

Author

Topic

chrisbarber1
Starting Member

7 Posts

Posted - 2006-07-31 : 07:16:20

Hi

I am quite new to the delights of SQL and wondered if some professional could suggest the best approach to tackling the following problem:

If I had the following table:

Runner Result1 Result2 Result3 Result4
Dave 4 4 3 2
Phil 2 3 3 3
Derek 2 4 6 7

And I wanted to add and display a column that would display “best 3 total results”, such as:

Runner Result1 Result2 Result3 Result4 Best3
Dave 4 4 3 2 11
Phil 2 3 3 3 9
Derek 2 4 6 7 17

Any help would be really appreciated.
I would be using asp.net vb.net and sqlserver2003

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 07:36:36

[code]create table tbl
(
Runner varchar(10),
Result1 int,
Result2 int,
Result3 int,
Result4 int
)
go

create function best_three(@r1 int, @r2 int, @r3 int, @r4 int)
returns int
as
begin
declare @r int

select @r = sum(r)
from
(
select top 3 r
from
(
select @r1 as r union all
select @r2 as r union all
select @r3 as r union all
select @r4 as r
) a
order by r desc
) b
return @r
end
go

insert into tbl
select 'Dave', 4, 4, 3, 2 union all
select 'Phil', 2, 3, 3, 3 union all
select 'Derek', 2, 4, 6, 7
go

select *, dbo.best_three(Result1, Result2, Result3, Result4) as best_three
from tbl

/*

Runner Result1 Result2 Result3 Result4 best_three
---------- ----------- ----------- ----------- ----------- -----------
Dave 4 4 3 2 11
Phil 2 3 3 3 9
Derek 2 4 6 7 17
*/

drop function best_three
go
drop table tbl
go
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 07:37:41

"I would be using asp.net vb.net and sqlserver2003"
by the way, there isn't any sqlserver2003.

chrisbarber1
Starting Member

7 Posts

Posted - 2006-07-31 : 07:49:44

Wow fast work!
You have made it look very easy.
Thanks for your help.
p.s. I meant 2000 whoops :o)

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-31 : 11:49:58

well...you should really think about normalizing your data

think in rows, not columns for this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-31 : 12:35:38

http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

chrisbarber1
Starting Member

7 Posts

Posted - 2006-08-01 : 03:17:15

Thanks again for the tuition. I can see I have a lot to learn but have at least made a start.

If I can dare ask would this be the right way to tackle my ultimate goal which to calculate the following results:

“There are two team championships, one for women and one for combined teams. The women's team for each club requires three runners, one of which must be over 40. The combined teams consist of seven men plus the three women. Of the seven men, at least one must be over 40 and one must be over 50. If a club does not have the required number of runners then, when calculating totals, the remaining places are taken to be (N+1) where N is the number of runners in that league race e.g. if a club has a missing male runner and there are 200 male runners (apart from guests) in the race then 201 will be added to that club's total. Teams are then listed in order of total placings at each race, with the lowest total being first. The team positions at each race are then used to determine the team championships at the end of the season.”

Also

quote:
think in rows, not columns

Can you explain that please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 04:02:05

That would be normalization. You might want to rewrite your database design as

::Runner table

RunnerID  RunnerName
--------  ----------
       1  Dave
       2  Phil
       3  Derek


::Result table

Try       RunnerID  Result
--------  --------  ------
       1         2       2
       3         2       3
       3         3       6
       2         2       3
       2         1       4
       2         3       4
       4         1       2
       4         3       7
       1         1       4
       1         3       2
       4         2       3
       3         1       3

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 04:09:01

Examine this test

-- prepare test data
declare @Runner table (RunnerID INT, RunnerName varchar(100))

insert	@runner
select	1, 'Dave' union all
select	2, 'Phil' union all
select	3, 'Derek'

declare @result table (try int, runnerid int, result int)

insert	@result
select	1, 2, 2 union all
select	3, 2, 3 union all
select	3, 3, 6 union all
select	2, 2, 3 union all
select	2, 1, 4 union all
select	2, 3, 4 union all
select	4, 1, 2 union all
select	4, 3, 7 union all
select	1, 1, 4 union all
select	1, 3, 2 union all
select	4, 2, 3 union all
select	3, 1, 3

-- do the work
select		ru.RunnerName,
		max(case when re.try = 1 then re.result end) Result1,
		max(case when re.try = 2 then re.result end) Result2,
		max(case when re.try = 3 then re.result end) Result3,
		max(case when re.try = 4 then re.result end) Result4,
		sum(case when re.try <= 3 then re.result end) Best3
from		@runner ru
inner join	@result re on re.runnerid = ru.runnerid
group by	ru.RunnerName
order by	ru.RunnerName

The query now is much more easy to read and maintain.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 04:18:24

Peter,

The poster wanted best 3 of all result. What you have is sum of first 3 result.

quote:
And I wanted to add and display a column that would display “best 3 total results”

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 04:30:24

Here is my attemp of it.

-- prepare test data
declare @runner table (runnerid int, runnername varchar(10))

insert	@runner
select	1, 'dave' union all
select	2, 'phil' union all
select	3, 'derek'

declare @result table (try int, runnerid int, result int)

insert	@result
select	1, 2, 2 union all
select	3, 2, 3 union all
select	3, 3, 6 union all
select	2, 2, 3 union all
select	2, 1, 4 union all
select	2, 3, 4 union all
select	4, 1, 2 union all
select	4, 3, 7 union all
select	1, 1, 4 union all
select	1, 3, 2 union all
select	4, 2, 3 union all
select	3, 1, 3

-- do the work
select	*
from
(
	select		ru.runnerid, ru.runnername,
			max(case when re.try = 1 then result end) result1,
			max(case when re.try = 2 then result end) result2,
			max(case when re.try = 3 then result end) result3,
			max(case when re.try = 4 then result end) result4
	from		@runner ru
	inner join	@result re on re.runnerid = ru.runnerid
	group by	ru.runnerid, ru.runnername
) a
inner join 	
(
	select	runnerid, sum(result) best_3
	from	@result r
	where	try in (select top 3 try from @result x where x.runnerid = r.runnerid order by result desc)
	group by runnerid
) b3	on	a.runnerid	= b3.runnerid

/*	RESULT :

runnerid    runnername result1     result2     result3     result4     runnerid    best_3      
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           dave       4           4           3           2           1           11
2           phil       2           3           3           3           2           9
3           derek      2           4           6           7           3           17

*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 04:33:42

Yes, I blame copy & paste. The query would be

-- do the work
select		ru.RunnerName,
		max(case when re.try = 1 then re.result end) Result1,
		max(case when re.try = 2 then re.result end) Result2,
		max(case when re.try = 3 then re.result end) Result3,
		max(case when re.try = 4 then re.result end) Result4,
		sum(re.result) - min(re.result) Best3
from		@runner ru
inner join	@result re on re.runnerid = ru.runnerid
group by	ru.RunnerName
order by	ru.RunnerName

Or simplified khtan solution

select		ru.runnerid,
		ru.runnername,
		max(case when re.try = 1 then re.result end) result1,
		max(case when re.try = 2 then re.result end) result2,
		max(case when re.try = 3 then re.result end) result3,
		max(case when re.try = 4 then re.result end) result4,
		min(b3.best_3) best3  -- or max... it doesn't matter.
from		@runner ru
inner join	@result re on re.runnerid = ru.runnerid
inner join 	(
			select		r.runnerid,
					sum(r.result) best_3
			from		@result r
			where		r.try in (select top 3 x.try from @result x where x.runnerid = r.runnerid order by x.result desc)
			group by	r.runnerid
		) b3 on b3.runnerid = ru.runnerid
group by	ru.runnerid,
		ru.runnername

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 04:40:06

"sum(result) - min(result) Best3"
That's a smart way

. Why didn't I think of that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 04:41:52

It's ok, khtan

I still learn from your solutions.

But SUM(re.Result) - MIN(re.Result) does only work for "sum of all result but last"...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 04:54:20

"Or simplified khtan solution"
Thanks. Now it is neater

. Too many derived table + sub query tend to get "Internal SQL Server Error" when the query grow in future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources