>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
