	AllExperts > Database Programming
	Search

About Iain M Watson
Expertise
I have 15 years of SQL experience in several large scale business catagories. I have many programs involving complicated SQL queires in both DB2 and Orcale databases. I have many years experience dealing with Database administrators in performance/tuning and implementing large scale SQL systems.

Experience
I have designed many complicated SQL queries involving many aspects of the SQL syntax to obtain information from large databases with performance and efficiency as a major consideration.

Education/Credentials
Bachelors degree in Computer Science.

You are here: Experts > Industry > Data Management > Database Programming > SQL Query

Database Programming - SQL Query

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.

Add to this Answer Ask a Question