Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Aggregate of a Aggregate producing strange results

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2009
    Posts
    64
    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.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •