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]
