--Sample Data
declare @t table (Item varchar(20),a int,b int, c int, d int)
insert @t
select 'Apple', 1, 0, 29, 3
union all select 'Banana', 4, 0 , 4, 100
union all select 'Carrot', 39,3,2,0
union all select 'Derringer', 3, 3, 5, 2

--Query
select Item, ((t.a + t.b + t.c + t.d - t.[Max_of_a_to_d]) * 1.0) / 3
from
(
select
	x.Item,
 	x.a,
 	x.b,
 	x.c,
 	x.d,
   	[Max_of_a_to_d] =  
	 	case
	 	when  x.a is not null   and
	  		(x.a >= x.b or x.b is null) and
	  		(x.a >= x.c or x.c is null) and
	  		(x.a >= x.d or x.d is null)
	 	then 	x.a
	 	when  	x.b is not null   and
	  		(x.b >= x.a or x.a is null) and
	  		(x.b >= x.c or x.c is null) and
	  		(x.b >= x.d or x.d is null)
	 	then 	x.b
	 	when  	x.c is not null   and
	  		(x.c >= x.a or x.a is null) and
	  		(x.c >= x.b or x.b is null) and
	  		(x.c >= x.d or x.d is null)
	 	then 	x.c
	 	when  	x.d is not null   and
	  		(x.d >= x.a or x.a is null) and
	  		(x.d >= x.b or x.b is null) and
	  		(x.d >= x.c or x.c is null)
	 	then x.d
	 	else null
	 	end
from
 	@t x
) t

--Result
Item                 Avg
-------------------- ---------------------------------------
Apple                1.333333
Banana               2.666666
Carrot               1.666666
Derringer            2.666666

quote:

---

Originally posted by ConradK

Thank you much!

---

DECLARE @t table(Item varchar(20), a int, b int, c int, d int)

INSERT INTO @t (Item, a, b, c, d)
SELECT 'Apple'		, 1, 0, 29, 3		UNION ALL
SELECT 'Banana'		, 4, 0, 4, 100		UNION ALL
SELECT 'Carrot'		, 39,3,2,0			UNION ALL
SELECT 'Derringer'	, 3, 3, 5, 2


SELECT Item, (SUM(Amt) - MAX(Amt))/(COUNT(*) * 1.00 - 1)
FROM (
		SELECT Item, a AS Amt
		  FROM @t
		UNION ALL
		SELECT Item, b AS Amt
		  FROM @t
		UNION ALL
		SELECT Item, c AS Amt
		  FROM @t
		UNION ALL
		SELECT Item, d AS Amt
		  FROM @t
) AS XXX
GROUP BY Item


  

DECLARE @t table(Item varchar(20), Amt int)

INSERT INTO @t (Item, Amt)
SELECT 'Apple'		,   1 UNION ALL
SELECT 'Apple'		,   0 UNION ALL
SELECT 'Apple'		,  29 UNION ALL
SELECT 'Apple'		,   3 UNION ALL
SELECT 'Banana'		,   4 UNION ALL
SELECT 'Banana'		,   0 UNION ALL
SELECT 'Banana'		,   4 UNION ALL
SELECT 'Banana'		, 100 UNION ALL
SELECT 'Carrot'		,  39 UNION ALL
SELECT 'Carrot'		,   3 UNION ALL
SELECT 'Carrot'		,   2 UNION ALL
SELECT 'Carrot'		,   0 UNION ALL
SELECT 'Derringer'	,   3 UNION ALL
SELECT 'Derringer'	,   3 UNION ALL
SELECT 'Derringer'	,   5 UNION ALL
SELECT 'Derringer'	,   2


SELECT Item, (SUM(Amt) - MAX(Amt))/(COUNT(*) * 1.00 - 1)
FROM @t
GROUP BY Item


  

quote:

---

Originally posted by ConradK
and do not think in normalized terms. I think I can work it from here!

---

quote:

---

COol!!!!

---

quote:

---

Originally posted by Peso

Brett, I think you missed one column. The "type" column where values {a, b, c, d} are stored, to know which value is attached to which type.

---

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

---

DECLARE	@t TABLE
	(
		Item VARCHAR(20) NOT NULL, 
		[Type] CHAR(1) NOT NULL,
		Amt INT NOT NULL
	)

INSERT	@t
	(
		Item, 
		[Type],
		Amt
	)
SELECT 'Apple',     'a',   1 UNION ALL
SELECT 'Apple',     'b',   0 UNION ALL
SELECT 'Apple',     'c',  29 UNION ALL
SELECT 'Apple',     'd',   3 UNION ALL
SELECT 'Banana',    'a',   4 UNION ALL
SELECT 'Banana',    'b',   0 UNION ALL
SELECT 'Banana',    'c',   4 UNION ALL
SELECT 'Banana',    'd', 100 UNION ALL
SELECT 'Carrot',    'a',  39 UNION ALL
SELECT 'Carrot',    'b',   3 UNION ALL
SELECT 'Carrot',    'c',   2 UNION ALL
SELECT 'Carrot',    'd',   0 UNION ALL
SELECT 'Derringer', 'a',   3 UNION ALL
SELECT 'Derringer', 'b',   3 UNION ALL
SELECT 'Derringer', 'c',   5 UNION ALL
SELECT 'Derringer', 'd',   2

quote:

---

Originally posted by ConradK

I've got four columns, a, b, c, and d. They look look like this

Item, a, b, c, d
Apple, 1, 0, 29, 3
Banana, 4, 0 , 4, 100
Carrot, 39,3,2,0
Derringer, 3, 3, 5, 2

I want to find an average value between them where the highest value is dropped out. The results would look like this

Apple, 1.33
Banana, 2.66
Carrot, 1.66
Derringer, 2.66

Thoughts?

---

SELECT Item, (a + b + c + d - max_value) / 3.0 AS average
  FROM table_name
     CROSS APPLY
        (SELECT MAX(value)
           FROM (VALUES (a), (b), (c), (d)
                ) D(value)
        ) D(max_value);

quote:

---

Originally posted by ms65g

quote:

---

Originally posted by ConradK

I've got four columns, a, b, c, and d. They look look like this

Item, a, b, c, d
Apple, 1, 0, 29, 3
Banana, 4, 0 , 4, 100
Carrot, 39,3,2,0
Derringer, 3, 3, 5, 2

I want to find an average value between them where the highest value is dropped out. The results would look like this

Apple, 1.33
Banana, 2.66
Carrot, 1.66
Derringer, 2.66

Thoughts?

---

SQL Server 2008 approach:

SELECT Item, (a + b + c + d - max_value) / 3.0 AS average
  FROM table_name
     CROSS APPLY
        (SELECT MAX(value)
           FROM (VALUES (a), (b), (c), (d)
                ) D(value)
        ) D(max_value);

______________________

---

DECLARE @t table(Item varchar(20), a int, b int, c int, d int)

INSERT INTO @t (Item, a, b, c, d)
SELECT 'Apple'		, 1, 0, 29, 3		UNION ALL
SELECT 'Banana'		, 4, 0, 4, 100		UNION ALL
SELECT 'Carrot'		, 39,3,2,0			UNION ALL
SELECT 'Derringer'	, 3, 3, 5, 2

select item,
	(
		select (sum(n)-max(n))/(count(n)*1.0-1) from 
		(
			values(a),(b),(c),(d)
		) as t(n)
	)  as maximum_value 
from @t 

select item,
	(
		select (sum(n)-max(n))/(count(n)*1.0-1) from 
		(
			values(a),(b),(c),(d)
		) as t(n)
	)  as maximum_value 
from @t 

quote:

---

Originally posted by X002548

I'll go look this up...but huh???

What does that reference?

select item,
	(
		select (sum(n)-max(n))/(count(n)*1.0-1) from 
		(
			values(a),(b),(c),(d)
		) as t(n)
	)  as maximum_value 
from @t 

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

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ms65g

quote:

---

Originally posted by ConradK

I've got four columns, a, b, c, and d. They look look like this

Item, a, b, c, d
Apple, 1, 0, 29, 3
Banana, 4, 0 , 4, 100
Carrot, 39,3,2,0
Derringer, 3, 3, 5, 2

I want to find an average value between them where the highest value is dropped out. The results would look like this

Apple, 1.33
Banana, 2.66
Carrot, 1.66
Derringer, 2.66

Thoughts?

---

SQL Server 2008 approach:

SELECT Item, (a + b + c + d - max_value) / 3.0 AS average
  FROM table_name
     CROSS APPLY
        (SELECT MAX(value)
           FROM (VALUES (a), (b), (c), (d)
                ) D(value)
        ) D(max_value);

______________________

---

Another method

DECLARE @t table(Item varchar(20), a int, b int, c int, d int)

INSERT INTO @t (Item, a, b, c, d)
SELECT 'Apple'		, 1, 0, 29, 3		UNION ALL
SELECT 'Banana'		, 4, 0, 4, 100		UNION ALL
SELECT 'Carrot'		, 39,3,2,0			UNION ALL
SELECT 'Derringer'	, 3, 3, 5, 2

select item,
	(
		select (sum(n)-max(n))/(count(n)*1.0-1) from 
		(
			values(a),(b),(c),(d)
		) as t(n)
	)  as maximum_value 
from @t 

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx

Madhivanan

Failing to plan is Planning to fail

---

select item, (a + b + c + d -
        case when a >=all (select b union select c union select d) then a
             when b >=all (select c union select d) then b
             when c >= d then c
             else d
        end) / 3.0 as avarage
from table_name