Hello I have the following table and data. I need to find the avg game attendance for homegame (where shcool = 'Indiana Univ.' and away games ( where opponent = 'Indiana Univ." This would be 3 columns listing the SCHOOL 'Indiana Univ.", AVG HOMEGAME ATTENDANCE, AVG AWAY ATTENDANCE. I have no clue how to format the query to get the last column.

- Thanks for your help and sugestions.

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 41000, 0, 7, null, null);

select 1,avg(goals) from table where it is home
union
select 2,avg(goals) from table where it is not home

UNION is nice, but it returns 2 rows

Try

SELECT 'Indiana Univ.' as school,
avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
from homegame;

It returns :

SCHOOL AVG_HOMEGAME AVG_AWAYGAME
Indiana Univ. 41000 46000

- Thanks for the Reply. Never used case before. I have tried moving the parethesis aronund bet keep getting the belower error?

SQL> SELECT 'Indiana Univ.' as school,
2 avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
3 avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
4 from homegame;
avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
*
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL>

I guess you are using an Oracle version that doesn't support CASE (e.g. 8.1.6)

Instead, you can use the good old DECODE.

This does not work :
SQL> SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL;
SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL
*
ERROR at line 1:
ORA-00907: missing right parenthesis

But this might work :
SQL> SELECT DECODE(1,1,'TRUE','FALSE') FROM DUAL;

DECO
----
TRUE

So, in your case :
SELECT 'Indiana Univ.' as school,
avg(decode(school,'Indiana Univ.',attendance,null)) AS avg_homegame,
avg(decode(opponent,'Indiana Univ.',attendance,null)) AS avg_awaygame
FROM homegame;