SQL & PL/SQL

I'm not sure about your requirement. But, at least you can provide your table structure along with some sample test data so that we can try your expected output.

Regards.

Satyaki De.

Hi,
I have got three tables named stat_totals,schedules, teams
stat_totals has the following columns schedule_id,stat_type_id ,column_id, player_team_coach(player_id),league_id,column_value,current_player_team_id
schedules has the following columns schedule_id,schedule_date,home_team_id,visitor_team_id,league_id,season_id
teams has the following columns team_id,team_name,location_id,division_id,league_id

select player_team_coach,current_player_team_id current_team_id,HOME_TEAM_ID,VISITOR_TEAM_ID,c.team_id,
SUM(to_number(column_value, '9999')) pr_yard,
0 avg_yds_unit from stat_totals a, schedules b, teams c
where a.schedule_id = b.schedule_id and a.stat_type_id = 'PPG'
and a.column_id = 'FPLKICK_PR_YARD' and b.league_id = 'NFL'
and a.SCHEDULE_ID = '20040809NFL--WASHINGTON0'
-- and b.schedule_date between '01-DEC-2007' and '31-DEC-2007'
and b.game_status_id = 143
and a.current_player_team_id in (
select CASE WHEN a.current_player_team_id = HOME_TEAM_ID THEN visitor_team_id
when a.current_player_team_id = visitor_team_id then HOME_TEAM_ID
else 0 end team_id from stat_totals a, schedules b, teams c
where a.schedule_id = b.schedule_id and a.stat_type_id = 'PPG'
and a.column_id = 'FPLKICK_PR_YARD' and b.league_id = 'NFL'
and a.SCHEDULE_ID = '20040809NFL--WASHINGTON0'
-- and b.schedule_date between '01-DEC-2007' and '31-DEC-2007'
and b.game_status_id = 143 and a.current_player_team_id = c.team_id
)
and a.current_player_team_id = c.team_id -- and c.primary_conf_id = 5
group by player_team_coach, current_player_team_id,HOME_TEAM_ID,VISITOR_TEAM_ID,c.team_id





player_id current_team_id home_team_id visitor_team_id c.team_id column_value
*13373 8831 8831 8810 8831 11*


*2026423 8810 8831 8810 8810 46*


*1953480 8831 8831 8810 8831 15*

whenever current_player_team_id = home_team_id then i need to total the opposing teams column_values
in the above example for current_team_id = 8831 ,the opposing team is 8810, so we need to sum up all the column_nalues of 8810 which is equivalent to 46.
similarly for current_team_id = 8810 the opposing team is 8831 , so we need to sum up all the column_nalues of 8831 which is equivalent to 26.

My output has to look like this
player_id current_team_id home_team_id visitor_team_id c.team_id column_value
*13373 8831 8831 8810 8831 46*


*2026423 8810 8831 8810 8810 26*


*1953480 8831 8831 8810 8831 46*

Hi,

Here's one way:

WITH  oq  AS
(   -- Begin sub-query oq: original query
    SELECT schedule_id ,COLUMN_VALUE,player_team_coach,current_player_team_id 
    FROM stat_totals where SCHEDULE_ID = '20040809NFL--WASHINGTON0' AND COLUMN_ID ='FPLKICK_PR_YARD';
)   -- End sub-query oq: original query
SELECT  team_id
,       (   -- Begin scalar sub-query
            SELECT  SUM (column_value)
            FROM    oq
            WHERE   team_id  != m.team_id
        )   AS sum_column_value      -- End scalar sub-query
,       player_team_coach
FROM    oq      m       -- m for main
;

Oops!

I skipped this part ->

SELECT schedule_id ,COLUMN_VALUE,player_team_coach,current_player_team_id
FROM stat_totals where SCHEDULE_ID = '20040809NFL--WASHINGTON0' AND COLUMN_ID ='FPLKICK_PR_YARD';

I need to sleep.

Perhaps Frank has given you the right solution.

Regards.

Satyaki De.

Got the problem solved, been very quick in replying .

Thanks
Sneha

Hi,

Are analytical functions permitted?

create table user579585 (
player_id number,
current_team_id number,
home_team_id number,
visitor_team_id number,
team_id number,
column_value number
);

insert into user579585 values (13373, 8831, 8831, 8810, 8831, 11);
insert into user579585 values (2026423, 8810, 8831, 8810, 8810, 46);
insert into user579585 values (1953480, 8831, 8831, 8810, 8831, 15);
commit;

select b.*,
sum(column_value) over (partition by home_team_id) -
sum(column_value) over (partition by current_team_id) aa
from user579585 b;


Regards,

Melvin