[SOLVED] Getting AVG with a GROUP BY

[soycharliente]

I'm looking for an average of averages (I hope that makes sense).

 

Each visit to a location has a rating. You can calculate the average rating for each location. I want to average the average ratings for the tally count groups. So, average the location averages that have tally counts of 1, of 2, of 6, etc.

 

I've tried two different queries, but neither give me exactly what I'm looking for and I'm stuck. I'll give you my DB structure, the two queries, the respective results, and why it's not what I'm looking for. If you can help, much appreciated.

 

DB structure:

TABLE `lunch_data` (
  `id` int( NOT NULL auto_increment,
  `pid` int(4) NOT NULL default '0',
  `thedate` date NOT NULL default '0000-00-00',
  `rating` int(4) default NULL,
  `spent` float default NULL,
  `comment` text,
  `publish` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `thedate` (`thedate`)
);

TABLE `lunch_locations` (
  `id` int(4) NOT NULL auto_increment,
  `loc` varchar(255) NOT NULL default '',
  `menu` text,
  `map` text,
  `tally` int( NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `loc` (`loc`)
);

TABLE `lunch_tally` (
  `id` int(4) NOT NULL auto_increment,
  `pid` int(4) NOT NULL,
  `tally` int(4) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `pid` (`pid`)
);

 

Query #1:

SELECT tee.`tally`,
AVG(`rating`) AS `avg_rating`
FROM `lunch_locations` el
JOIN `lunch_data` dee ON el.`id`=dee.`pid`
JOIN `lunch_tally` tee ON el.`id`=tee.`pid`
GROUP BY `tally`

This is almost correct because it gives one average per tally count group. This is wrong because upon inspection of the results, the averages returned are not correct. I have 4 locations with a tally of 6. If I manually calculate the average and compare, it's off.

 

Query #2:

SELECT tee.`tally`,
AVG(`rating`) AS `avg_rating`
FROM `lunch_locations` el
JOIN `lunch_data` dee ON el.`id`=dee.`pid`
JOIN `lunch_tally` tee ON el.`id`=tee.`pid`
GROUP BY `loc`

This is wrong because it returns the average rating total for each location.

 

Anyone that can help is awesome. I know this is a lot to read. Thanks for trying to help.

[gassaz]

Maybe if you can reestructure the query like this..

 

SELECT AVG(sum_column1)
       FROM (SELECT SUM(column1) AS sum_column1
             FROM t1 GROUP BY column1) AS t1;

 

i hope this will help you..