Aggregate of a Aggregate producing strange results

ozzii · #1 (**permalink**) 12-09-09, 15:36

Hi

I have the following mySQL table:

Code:

CREATE TABLE `enquiries` (
  `enquiry_id` int(10) unsigned NOT NULL auto_increment,
  `enquiry_date` datetime NOT NULL,
  `enquiry_type` varchar(50) default NULL,
  PRIMARY KEY  (`enquiry_id`)
)

I am trying to work out the average enquiries by day of week. I use the following sql. This produces correct results. for example if i have a total of 10 enquires over 2 Mondays the average enquiries for a Monday will be 5. You will note from the following SQL, within the inline statement I have grouped the qury using the enquiry_date.

Code:

select day_of_week, 
       day_name,
       AVG(total_enquiries) as avg_enquiries
from (select  dayofweek(enquiry_date) as day_of_week,
              dayname(enquiry_date) as day_name,
              count(*) as total_enquiries
      from enquiries
      group by 
      enquiry_date) il
group by
  day_of_week, 
  day_name

Now heres the question if I use the following SQL instead of an average I end up with a total i.e 10 enquires for a Monday. You will note in the following SQL, within the inline statment I have done the grouping this time using the aliases of day_of_week and day_name.

Code:

select day_of_week, 
       day_name,
       AVG(total_enquiries) as avg_enquiries
from (select  dayofweek(enquiry_date) as day_of_week,
              dayname(enquiry_date) as day_name,
              count(*) as total_enquiries
      from enquiries
      group by 
      day_of_week,
      day_name) il
group by
  day_of_week, 
  day_name

Can any one explain to me why you get this phenomena. Why cant I get an average using either of the SQL statements.

Any help would be appreciated. I have attached a dump to replicate.

mnirwan · #2 (**permalink**) 12-09-09, 21:35

On your bottom query .... It produces 1 entry for Monday with enquiry 10. When you average 1 entry for Monday with value 10, you will get 10.

On your middle query, however, it produces 2 dates (that happen to be Mondays) with enquiry 3 and 7 (or whatever other values totaling to 10). When you have 2 entries totaling 10, the average is 5.

Try to run the sub query by itself on each of your query. You'll see why.

dav1mo · #3 (**permalink**) 12-10-09, 11:55

you would, also, need a count of the days of the week. In your instance here that would be a 2, maybe another subquery and then calculate avg on your own?
Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech