quote:

---

Originally posted by dataguru1971

What determines "the lower half"?

---

Poor planning on your part does not constitute an emergency on my part.

---

SELECT sitename as 'Site Name',datename(weekday,transdate) as 'Days',
actualweather as 'Weather',TOP 50 PERCENT ROUND(AVG(statlh),0) as 'Average' FROM abcperformance
WHERE (sitename like '%gessner%' AND datename(weekday,transdate)='Friday' AND actualweather='2 Mostly Sunny' )
GROUP BY datename(weekday,transdate),sitename,actualweather
ORDER BY Average,actualweather

quote:

---

Originally posted by jimf

select avg(col1)

from
(
select top 50 percent col1
from yourTable
order by col1 asc
) a

Jim

Everyday I learn something that somebody else already knew

---

quote:

---

Originally posted by jcelko

Here is a solution with no proprietary code.

CREATE TABLE Foobar (i INTEGER NOT NULL);
INSERT INTO Foobar VALUES (), ...;

SELECT AVG(i) AS lower_i_avg
FROM (SELECT i, ROW_NUMBER () OVER (ORDER BY i DESC),
COUNT(*) OVER()/2
FROM Foobar)
AS F1(i, seq, cut)
WHERE seq >= cut;


Did you know about single quotes, double quotes and square brackets? There is no need to write dialect. Did you notice that your query is full of constants? There is no need to pull them out of the table; just give the constant columns a name.

SELECT site_name, 'Friday' AS weekday_name, 'Mostly Sunny' AS actual_weather_txt,
(..) AS lower_statlh_avg
FROM ABC_Performance
WHERE site_name LIKE '%GESSNER%'
AND DATENAME(WEEKDAY, trans_date)= 'Friday'
AND actual_weather_txt = 'Mostly Sunny'
GROUP BY site_name;


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

---

quote:

---

Originally posted by jimf

select avg(col1)

from
(
select top 50 percent col1
from yourTable
order by col1 asc
) a

Jim

Everyday I learn something that somebody else already knew

---

quote:

---

Originally posted by Lumbago

SELECT Sitename, Days, Weather, ROUND(AVG(statlh),0) as 'Average'
FROM (
  SELECT TOP 50 PERCENT sitename,datename(weekday,transdate) as 'Days',
  actualweather as 'Weather', statlh
  FROM abcperformance
  WHERE (sitename like '%gessner%' AND datename(weekday,transdate)='Friday' AND actualweather='2 Mostly Sunny' )
  ORDER BY statlh
) as a
GROUP BY Days,sitename,weather
ORDER BY Average,Weather

- Lumbago

My blog-> www.thefirstsql.com

---