You need DISTINCT to make the COUNT() function ignore duplicate values. SELECT e.e_id , e.e_code , COUNT(DISTINCT qxe.q_id) as e_count , avg(ratings.r_quality) as avqual FROM e, qxe, ratings WHERE e.e_id = qxe.e_id AND ratings.e_id = e.e_id AND ratings.q_id = qxe.q_id AND ratings.e_id = qxe.e_id GROUP BY e.e_id, e.e_code;
That way you see how many different "q_id"'s you have and not how many rows were used in the ratings calculations. It is also good form to always list all un-aggregated columns in your GROUP BY clauses. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor <[EMAIL PROTECTED] To: t> cc: [EMAIL PROTECTED] Fax to: 07/12/2004 09:42 Subject: Re: query gets count wrong AM Please respond to platypus I am an anal single query-oholic. I know I could do this in 2 queries I have a query involving several related tables and I have attempted to reduce it down to what causes "not what I want" results. I am attempting to fill a summary table. For each main item in this table I want to count the number of child items in a related table that point to it. This is fine. I have a third table called ratings which rates individual child items and it is the problem. The table qxe contains e_id which is a foreign key to the main table and q_id which is a foreign key to the actual child items. I had to set things up this way because a q might belong to more than one e. Right now I am not even interested in anything about the q's data I just want to count them. The ratings table is the problem because any q can have an arbitrary number of ratings. so a rating has q_id and e_id as well as another key that combines to form a multipart key. instead of the number of q_ids in the qxe table that have e_id equal to the the current e_id, I am getting as e_count the total number of ratings for that e_id which is a huge humber. I know I need another constraint but I cant figure out what it is. What constraint can I add to make this query do what I want while still being able to average the ratings. SELECT e.e_id, e.e_code, COUNT(qxe.q_id) as e_count, avg(ratings.r_quality) as avqual FROM e, qxe, ratings WHERE e.e_id = qxe.e_id AND ratings.e_id = e.e_id AND ratings.q_id = qxe.q_id AND ratings.e_id = qxe.e_id GROUP BY e.e_id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]