>FB 1.5x
>
>I have a SQL statement that returns the results I want--giving me a count on the detail dataset
>(ClientRegHistList is a detail list on ClientRegHis, more info below)
>
>  select C.RegDate, Count(Client_ID)
>    from ClientRegHist C,
>         ClientReghistList CL
>   where (CL.ClientRegHist_ID = C.ClientRegHist_ID)
>     and CL.RegType = 0
>     and (C.Company_ID = 128)
>group by 1;
>
>I have a second SQL that is exactly the same except changing the "0" to a "1" in line 5.  I would like to combine the two datasets into o ne result set--any hints on how to do this?
>
>Thank you,
>
>Ed Dressel

Huan is right in that your problem description is ambiguous, Ed, there are several possible answers to your question. However, I think there are a limited number of possible ways to read your question, so below are a couple of possibilities that may or may not work. My memory is poor, I haven't used Fb 1.5 for one or two years and I'm never use COUNT(<fieldname>) myself (I always use COUNT(*) or COUNT(DISTINCT <fieldname>), so no guarantees that all four of them will work. Even though not strictly speaking required, I also changed from SQL-89 to SQL-92 (explicitly using JOIN rather than joining in the WHERE clause).

a) Getting the sum of 0 and 1:

select C.RegDate, Count(Client_ID)
from ClientRegHist C
join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID
where CL.RegType in (0, 1)
and C.Company_ID = 128
group by 1;

b) Counting each separately:

select C.RegDate, CL.RegType, Count(Client_ID)
from ClientRegHist C
join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID
where CL.RegType in (0, 1)
and C.Company_ID = 128
group by 1, 2;

c) Having all the lines of both queries in the same result set (you may not be able to separate them):

select C.RegDate, Count(Client_ID)
from ClientRegHist C
join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID
where CL.RegType = 0
and C.Company_ID = 128
group by 1
union all
select C.RegDate, Count(Client_ID)
from ClientRegHist C
join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID
where CL.RegType = 1
and C.Company_ID = 128
group by 1;

d) Having one column for each in the result set:

select C.RegDate, Count(case when CL.RegType = 0 then Client_ID else null end) as RegType0, Count(case when CL.RegType = 1 then Client_ID else null end) as RegType1,
from ClientRegHist C
join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID
where CL.RegType in (0, 1)
and C.Company_ID = 128
group by 1;

Do any of these four answer your question?

HTH,
Set