OraFAQ Forum: SQL & PL/SQL » Count Records with value reference

Home » SQL & PL/SQL » SQL & PL/SQL » Count Records with value reference

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count Records with value reference [message #9547]

Tue, 18 November 2003 23:57

eggohead
Messages: 3
Registered: October 2003

Junior Member

Hello I have the following query and output. In addition to the code below I need a where clause or condition to also count players that have >=2 "Kneee Injury" from player_injury.injury_type. I do not need to display this column but need to include its condition in the code below? - Any suggestions? - Thanks

break on school on player
SELECT DISTINCT SCHOOL.SCHOOL, PLAYER.PLAYER, PLAYER_INJURY.INJURY_TYPE
FROM SCHOOL, PLAYER, PLAYER_INJURY
WHERE PLAYER.PLAYER = PLAYER_INJURY.PLAYER
AND PLAYER.SCHOOL = SCHOOL.SCHOOL
AND player.player IN(select player.player from player, player_injury
where PLAYER.PLAYER = PLAYER_INJURY.PLAYER
group by player.player
having count(*) > 6)
order by school, player, injury_type;

SCHOOL PLAYER INJURY_TYPE
------------------------------ ------------------------------ ----------------------------------------
Penn State Univ. Johnson, P. Cracked Rib
Dislocated Shoulder
Twisted Ankle
Pondunk Univ. Trapp, M. Eye Injury
Knee Injury
Leg Injury
Thumb Injury

Report message to a moderator

Re: Count Records with value reference [message #9550 is a reply to message #9547]

Wed, 19 November 2003 01:40

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How about this:

SELECT DISTINCT SCHOOL.SCHOOL
     , PLAYER.PLAYER
     , PLAYER_INJURY.INJURY_TYPE
  FROM SCHOOL, PLAYER
     , PLAYER_INJURY
 WHERE PLAYER.PLAYER = PLAYER_INJURY.PLAYER
   AND PLAYER.SCHOOL = SCHOOL.SCHOOL
   AND PLAYER.PLAYER IN( SELECT PI.PLAYER
                           FROM PLAYER_INJURY PI
                          WHERE PLAYER.PLAYER = PI.PLAYER
                            <B>AND PI.INJURY_TYPE = 'Knee Injury'</B>
                          GROUP BY PI.PLAYER
                         HAVING COUNT(*) > <B>2</B>)
 ORDER BY school, player, injury_type;

Report message to a moderator

Re: Count Records with value reference [message #9556 is a reply to message #9550]

Wed, 19 November 2003 07:53

eggohead
Messages: 3
Registered: October 2003

Junior Member

Hello I still need to list players that have 6 injuries or more then have a condition where at least 2 of them are knee injuries? Currently the query below list each players injuires distinctivley >6 The 'Knee Condition' can not affect this only weed out those people from the below query that do not meet the conditin of > 2 'Knee Injuries'. Hope I made this clear, thanks for your help.

break on school on player
SELECT DISTINCT SCHOOL.SCHOOL, PLAYER.PLAYER, PLAYER_INJURY.INJURY_TYPE
FROM SCHOOL, PLAYER, PLAYER_INJURY
WHERE PLAYER.PLAYER = PLAYER_INJURY.PLAYER
AND PLAYER.SCHOOL = SCHOOL.SCHOOL
AND player.player IN(select player.player from player, player_injury
where PLAYER.PLAYER = PLAYER_INJURY.PLAYER
group by player.player
having count(*) > 2)
order by school, player, injury_type;

Report message to a moderator

Previous Topic:	what's the order of the return record from select
Next Topic:	Using Long column in WHERE CLAUSE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 06:01:59 CDT 2024