Select Problem of COUNT on GROUP
 

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

Re: Select Problem of COUNT on GROUP
 

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'