Hi
Thanks to Arno and Svein.
I understand group by properly now.

After a bit more playing around I got exactly what I want.

I now have 3 queries:

1:
select
d.PlannedAssessor,
count(*) as DidNotMakeAppointMent
from DAR d inner join ACCIDENTCLAIMPROCESS a on d.darno=a.darno
where (d.plannedAssessor <> a.assessor)
GROUP BY d.PlannedAssessor

2:
select count(darno)as AssesmentCount,plannedassessor
from dar
where plannedassessor is not null
group by plannedassessor

3:
select
d.PlannedAssessor,
count(*)
from DAR d inner join ACCIDENTCLAIMPROCESS a on d.darno=a.darno
where (d.Assessor_DT > a.ASSESSMENTDATE)
GROUP BY d.PlannedAssessor

I created a view for each one.
My only problem left is to join the 3 views in one statement I am
working through the example in Embedded SQL Guide. I was wondering could
I join the 3 queries without using views?

All tables are joined on PlannedAssessor.



Svein Erling Tysvaer wrote:

> Hi Gerhardus,
>
> when using GROUP BY you must group by all fields not aggregate, i.e.
>
> SELECT A, count(distinct B)
> FROM MyTable
> GROUP BY A
>
> is legal, whereas
>
> SELECT A, C, count(distinct B)
> FROM MyTable
> GROUP BY A
>
> is illegal, since C is neither aggregate nor in the GROUP BY.
>
>
>>Would it be possible to have something like a
>>count(d.Assessor_DT < a.AssesmentDate) and a
>>count(d.PlannedAssessor <> a.Assessor)
>
>
> No, you cannot have comparison within the count, you would have to do
> something like
>
> select
> d.Darno,
> d.PlannedAssessor,
> a.Assessor,
> count(*)
> from DAR d inner join ACCIDENTCLAIMPROCESS a on d.darno=a.darno
> where (d.Assessor_DT < a.AssesmentDate)
> GROUP BY d.Darno, d.PlannedAssessor, a.Assessor,
>
> Hope this answers your questions, even though I didn't quite understand
> what you meant,
> Set