0

hi, i am a user of Toad for Oracle 8.6.1
i am having a problem contructing this query

SELECT
FAC.FACILITY_ABBR hall_abbr,
COUNT(HALLUNIT.ACCEPTS_MALES_FLAG) MISDEMEANOR_MALE,
COUNT(HALLUNIT.ACCEPTS_FEMALES_FLAG) MISDEMEANOR_FEMALES
FROM
HALL_UNITS HALLUNIT,
FACILITIES FAC
WHERE
HALLUNIT.HALL_ID = FAC.ID
AND HALLUNIT.ACCEPTS_MALES_FLAG ='T'
AND HALLUNIT.ACCEPTS_FEMALES_FLAG ='T'GROUPBY
FAC.FACILITY_ABBR

the result is:

hall_abbr MISDEMEANOR_MALE MISDEMEANOR_FEMALES
BJNJH 1 1
CJH 2 2
LPJH 2 2

that suppose to be:

hall_abbr MISDEMEANOR_MALE MISDEMEANOR_FEMALES
BJNJH 1 3
CJH 2 3
LPJH 2 3

its copies the result of MISDEMEANOR_MALE to MISDEMEANOR_FEMALES.. and i cant figure out why..

please help me...

thanks

2
Contributors
1
Reply
2
Views
11 Years
Discussion Span
Last Post by hollystyles
0

This is a logic error you are asking where MALE AND FEMALE = 'T'

By definition that filters out any records where MALE <> 'T' OR FEMALE <> 'T'

try this, let me know if it works

SELECT
FAC.FACILITY_ABBR hall_abbr,
COUNT(HALLUNIT.ACCEPTS_MALES_FLAG) MISDEMEANOR_MALE,
COUNT(HALLUNIT.ACCEPTS_FEMALES_FLAG) MISDEMEANOR_FEMALES
FROM
HALL_UNITS HALLUNIT,
FACILITIES FAC
WHERE
HALLUNIT.HALL_ID = FAC.ID
AND HALLUNIT.ACCEPTS_MALES_FLAG ='T'
OR
HALLUNIT.HALL_ID = FAC.ID
AND HALLUNIT.ACCEPTS_FEMALES_FLAG ='T'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.