Brutal but looks like one way to do views. I think working in D1 the
most fustrating bit is the 'capabilty not supported' message on queries
that work in D4 with Paradox 7 tables.
Anyway, thanks for you help (& the list of SQL sites that am looking at
now). I actually did a quick & dirty solution that generates one of the
counts (ie 'boys') and when needed (ie on GetText)& fire a query to get
the other count. Speed is OK for reasonably largish amounts of data.
Thanks again,
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: Mike Osborne [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 20, 1999 7:19 AM
> To: Multiple recipients of list delphi
> Subject: Re: [DUG]: Another query
>
>
> 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
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
