Given you're in D1, sometimes the less elegant but brutally effective is the
way to go.
Broadly, how I would do it this is.
Query1
SELECT Teacher, Count(Students) As NoGirls, 0 As NoBoys
FROM MySchoolTable
WHERE MaleFemale = "Girl"
GROUP BY Teacher
Then Use BatchMove 'Copy' to a Destination Table "MyTemp" with Query1 as
Source.
(Not sure if you also need a BatchMove 'Append' to populate "MyTemp")
MyTemp.AddIndex('byTeacher', 'Teacher');
MyTemp.Indexname('byTeacher');
(Not sure the syntax of the above two lines is watertight - but that's the
idea. MyTemp needs to be indexed on Teacher for the next BatchMove to work.)
Query2
SELECT Teacher, Count(Students) As NoBoys
FROM MySchoolTable
WHERE MaleFemale = "Boy"
GROUP BY Teacher
Then do BatchMove 'AppendUpdate' to "MyTemp" from Query2. (Need
AppendUpdate, vs just Update to handle Teachers with all boys)
That will give you MyTemp looking like
teacher | girls | boys
----------------------------
Mr X, 14, 16
Mrs Y, 20, 10
Read MyTemp & then delete it.
You're up for the cost of writing the file - but BatchMove is quick so
efficiency is probably OK unless you're dealing with very large datasets.
HTH,
Mike
-----Original Message-----
From: Grant Black <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Date: Wednesday, August 18, 1999 3:23 PM
Subject: [DUG]: Another query
I am having problems with queries again - (I need a decent book on SQL -
any recommendations?).
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)
This would return a list of teachersand the number of girls they have
teacher | girls
----------------------------
Mr X, 14
Mrs Y, 20
which is the number of girls
probablm is that I want the result set to return the number of girls
_and_ boys - ie:
teacher | girls | boys
----------------------------
Mr X, 14, 16
Mrs Y, 20, 10
Can I do something like
... Count(MaleFemale) As NumberOfBoys where MaleFemale = Boy?
If any one is interested the actual table looks like:
SELECT CadID, Cads.PcbID, Cads.LastCollection, Count(SeqNO) As
CollCount
FROM Telfiles LEFT OUTER JOIN Cads
ON Telfiles.cadid = cads.cadid
WHERE FileType < 70
GROUP BY cadiD, PCBID, LastCollection
BTW - thanks for the help on the my previous query of queries as the
responses were very useful.
Grant Black
Software Developer
SmartMove (NZ) Ltd
Phone: +64 9 361-0219 extn 719
Fax : +64 9 361-0211
Email: [EMAIL PROTECTED]
---------------------------------------------------------------------------
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
