Expert: Iain M Watson - 9/23/2009

Question
In a view I want to count the number of activities (CMTR_WR_ActivityType) per person (CMTR_WR_PersonID), per wave activity (CMTR_WR_WaveItemID) and only select records where the Activity count (CMTR_WR_ActivityType) is equal to 1.

The view is made up of 5 tables which I have already joined;
Person, Address, Company, Contract and CMTR_WaveResult

I am completely stumped, please help?

Regards,

Louise

Answer
Hi, I am not sure which database you are using, but most SQL is the same in relational databases.

You need to use the count and group by verbs to the existing joins you are doing:-


Select count(*) CMTR_WR_ActivityType, CMTR_WR_PersonID, CMTR_WR_WaveItemID

where CMTR_WR_ActivityType = 1
and  .....  ( all the key joins you need to get the rest of thes data rquired.)

group by  CMTR_WR_ActivityType, CMTR_WR_PersonID, CMTR_WR_WaveItemID;

Basically the GROUP BY will determine all the fields you want to count and in what order you want them grouped in.

Thanks.