I'd recommend the following:
SELECT Teacher, MaleFemale, Count(*)
FROM MySchoolTable // a table listing teachers, students
WHERE MaleFemale = 'Girl'
GROUP BY Teacher, MaleFemale;
Given the following table:
Mr. Brown, Girl, Sue
Mr. Brown, Girl, Jane
Mr. Brown, Girl, Lisa
Mr. Brown, Boy, John
Mrs. Black, Girl, Julie
Mrs. Black, Girl, Anna
Mrs. Black, Boy, Greg
Mrs. Black, Boy, Mike
The above SQL should return the following records:
Mr. Brown Girl 3
Mr. Brown Boy 1
Mrs. Black Girl 2
Mrs. Black Boy 2
You could throw a WHERE MALEFEMALE='Girl' on the end of the query to get the
girls only.
Note that the Group By limits your ability to include OtherStuff unless
OtherStuff is of the same uniqueness as Teacher | MaleFemale.
Cheers.
BJ....
-----Original Message-----
From: Grant Black <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Date: Thursday, 19 August 1999 08:39
Subject: RE: [DUG]: Another query
>Its close but it seems to be doing a union rather than a left outer join
>(I think!).
>
>On my sample data I have 5 'males' per 'teacher' then the query returns
>a count of 25 rather than 5. Gives me an idea though so I will have a
>play around with it before I go home tonight.
>
>Thanks,
>Grant
>
>Grant Black
>Software Developer
>SmartMove (NZ) Ltd
>Phone: +64 9 361-0219 extn 719
>Fax : +64 9 361-0211
>Email: [EMAIL PROTECTED]
>
>
>> -----Original Message-----
>> From: Aaron Scott-Boddendijk [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, August 18, 1999 4:30 PM
>> To: Multiple recipients of list delphi
>> Subject: Re: [DUG]: Another query
>>
>>
>> >I am having problems with queries again - (I need a decent
>> book on SQL -
>> >any recommendations?).
>>
>> I have 'ORACLE: the complete reference' which is a bit out of
>> date but has lots
>> of DBA type tricks for diagnostics... Covers SQL in a very
>> technical manner assuming
>> you already understand a great deal... If you've got ORACLE
>> it's great otherwise find
>> another book...
>>
>> >I have query that (in a simplified version) does;
>> >SELECT Teacher, Students, OtherStuff, Count(MaleFemale) As
>> >NumberOfGirls
>> >FROM MySchoolTable // a table listing teachers, students
>> >WHERE MaleFemale = 'Girl'
>> >GROUP BY (Teacher)
>>
>> something like
>>
>> SELECT
>> PMST.TEACHER Teacher,
>> COUNT(MMST.Student) NumberOfBoys,
>> COUNT(FMST.Student) NumberOfFemales
>> FROM
>> MySchoolTable PMST,
>> MySchoolTable MMST,
>> MySchoolTable FMST
>> WHERE
>> PMST.Teacher=MMST.Teacher AND MMST.MaleFemale='MALE'
>> AND
>> PMST.Teacher=FMST.Teacher AND FMST.MaleFemale='FEMALE'
>> GROUP BY
>> PMST.Teacher;
>>
>> I think it'll work...
>>
>> --
>> Aaron Scott-Boddendijk
>> Jump Productions
>> (07) 838-3371 Voice
>> (07) 838-3372 Fax
>>
>>
>> --------------------------------------------------------------
>> -------------
>> New Zealand Delphi Users group - Delphi List -
>> [EMAIL PROTECTED]
>> Website: http://www.delphi.org.nz
>>
>---------------------------------------------------------------------------
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
