IT Professionals' Lounge

IT Professionals' Lounge RSS

Select Problem of COUNT on GROUP

•

Join Date: Jul 2006

Posts: 1

Reputation: Jerrymie is an unknown quantity at this point

Solved Threads: 0

Jerrymie

Offline

Newbie Poster

Select Problem of COUNT on GROUP

Jul 12th, 2006

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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Select Problem of COUNT on GROUP

Jul 12th, 2006

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

Help with Code Tags

(Toggle Plain Text)

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'

Last edited by hollystyles; Jul 12th, 2006 at 10:55 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey