Hi,

I am using DBISAM

What I am trying to do is

SELECT
MyField AS MyField,
COUNT(*) AS ALLRECS,
(SELECT COUNT(*) FROM DATA WHERE ABC=0) AS CNT
FROM MyTable


I know this doesn't work but you can see what I am trying to do.
Is there a DBISAM SQL work around to achieve this result?

Thanks very much.

Sorry that should be

SELECT
MyField AS MyField,
COUNT(*) AS ALLRECS,
(SELECT COUNT(*) FROM DATA WHERE ABC=0) AS CNT
FROM DATA

"Dave Bailey" <david@itfx.com.au> wrote in message
news:8E705BED-5208-446A-A127-2595C750802E@news.elevatesoft.com...
> Hi,
>
> I am using DBISAM
>
> What I am trying to do is
>
> SELECT
> MyField AS MyField,
> COUNT(*) AS ALLRECS,
> (SELECT COUNT(*) FROM DATA WHERE ABC=0) AS CNT
> FROM MyTable
>
>
> I know this doesn't work but you can see what I am trying to do.

I for one don't see what you're trying to do. Do you want a final result
containg rows with three columns - Myfield, allrecs (total number of records
IN THE TABLE) and cnt (total number of recs IN THE TABLE where ABC = 0)?

Does not make much sense to me, but it can easily be done with a script.

But I suspect that what you really want is the counts of all records and all
abc=0 records GROUPED BY myfield. If that is the case

SELECT
MyField,
COUNT(*) AS ALLRECS,
SUM (IF(ABC=0 then 1 else 0)) AS CNT
FROM MyTable
GROUP BY MyField

Robert

> Is there a DBISAM SQL work around to achieve this result?
>
> Thanks very much.
>

Hi Robert,

I think what you have given me will work.
I thank you very much for your help.
Sorry I did not make myself clear.

Best Regards
Dave