List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 12 2004 8:22pm
Subject:Re: query gets count wrong
View as plain text  
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                                                       
                            
                      <platypus@stripped        To:                                     
                               
                      t>                       cc:       mysql@stripped         
                               
                                               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/mysql?unsub=1






Thread
Cost of joins?Margaret MacDonald9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
    • RE: Cost of joins?Lachlan Mulcahy12 Jul
      • Re: Cost of joins?Martijn Tonies12 Jul
      • Re: query gets count wrongGerald Taylor12 Jul
  • Re: Cost of joins?Alec.Cawley9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
    • Re: Cost of joins?Jochem van Dieten9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
      • Re: Cost of joins?Jochem van Dieten9 Jul
    • Re: Cost of joins?(Michael Johnson)9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
RE: Cost of joins?Bob)9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: query gets count wrongSGreen12 Jul