Average on MAX by Group

[JulianMcCurdy]

Hi,

I have a database with 3 tables, Contacts, Hives, and Inspections with Contact ID as the key.

Contacts
ContactID | RunGroup

Hives
HiveID | ContactID

InspectionsNZ
ContactID | HiveID | Hive Strength | Date

Each contact has multiple hives and each hive has multiple inspections.

I am trying to average the hive strength from the latest of all inspections belonging to contacts in an individual group

So far i have

SELECT 
ROUND(AVG(HiveStrength), 1) strength 
FROM 
InspectionsNZ
LEFT JOIN Contacts ON Contacts.ContactID = InspectionsNZ.CustomerID 
WHERE 
Contacts.RunGroup = '$run'
AND
InspectionsNZ.Date IN (SELECT MAX(Date) FROM InspectionsNZ LEFT JOIN Contacts ON InspectionsNZ.CustomerID = Contacts.ContactID WHERE RunGroup = '$run' AND Contacts.Status = 'Active' AND Type = 'Rental')
AND
Contacts.Status = 'Active'
AND 
Type = 'Rental'

At the moment i am not getiing accurat averages, im not sure what it is averaging but its not giving the correct results.

Thanks for your help. Loving this forum.

Julian

[Rabbit]

Can you post some sample data and results?

[JulianMcCurdy]

These are the Hive Strengths of the latest inspections from one group:

Hive Strenght | Date

0 2012/1/12
0 2012/1/12
8 2012/1/12
9 2012/1/12
5 2012/1/12
4 2012/1/12
1 2012/1/12
0 2012/1/12
8 2012/1/12
6 2012/1/12
0 2012/1/12
0 2012/1/12
7 2012/1/12
2 2012/1/12


This should average 3.5 but it is giving me and average of 0


Group Hives|Customers|Avg Hive Strength
Waitakere 14 7 0
Mt Albert 18 9 5
Ponsonby 18 9 0
Manukau 20 10 8
North Shore 26 13 1.5
Remuera 29 15 3
Titirangi 29 15 3
Kumeu 30 13 5.458
Mt Eden 31 16 5.714


This is the table i am outputting the data too

[Rabbit]

Have you tried the select statement without the aggregation to see what records it returns? To make sure it's returning the correct records to average on??

[JulianMcCurdy]

you mean take out the MAX from the sub query?

Also, would it matter if "hiveStrength' was 0 ?

[JulianMcCurdy]

Ok, have taken out the AVG (i get what you meant now) and for the example i gave it is averaging 0 0 instead of


0 2012/1/12
0 2012/1/12
8 2012/1/12
9 2012/1/12
5 2012/1/12
4 2012/1/12
1 2012/1/12
0 2012/1/12
8 2012/1/12
6 2012/1/12
0 2012/1/12
0 2012/1/12
7 2012/1/12
2 2012/1/12

[JulianMcCurdy]

Ok i think i know what the problem might be.... the sub query is looking for the MAX date so it will average only the HiveStrengths of inspections done on the latest date for each group.

Instead it should be averaging the hive strength of the latest inspection for each hive in the group... if that makes any sense?

[Rabbit]

So when you took out the average, it was returning the correct records? Or is it returning the wrong records?

[JulianMcCurdy]

wrong,

should be 0 0 8 9 5 4 1 0 8 6 0 0 7 2

what is getting is 0 0

[Rabbit]

Oh, I see what it is now. In your Max(Date), use Max([Date]) instead. Date is a reserved function, you should try not to use it as a column name. So to bypass it, you need to demarcate it as a field.

[JulianMcCurdy]

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Date]) FROM InspectionsNZ LEFT JOIN Contacts ON InspectionsNZ.CustomerID = Con' at line 9

[Rabbit]

Oh mySQL, I believe they use ` ` to demarcate a field.

[JulianMcCurdy]

nope still get the 0 0 with 'Date' and `Date` good thinking though...

Thanks for your help...

[Rabbit]

Run just the subquery and see if it returns the correct date. And use `Date`

[JulianMcCurdy]

I am getting only one date for each group where i should be getting all MAX inspection Dates for each hive in the group.

SELECT MAX(`Date`) strength FROM InspectionsNZ LEFT JOIN Contacts ON InspectionsNZ.CustomerID = Contacts.ContactID WHERE RunGroup = '$run'

Group Hives Customers Avg Hive Strength
Kumeu 30 13 19-12-11
Manukau 20 10 20-12-11
Mt Albert 18 9 19-01-12
Mt Eden 31 16 19-01-12
North Shore 26 13 22-01-12
Ponsonby 18 9 12-01-12
Remuera 29 15 22-01-12
Titirangi 29 15 22-01-12
Waitakere 14 7 12-01-12

[Rabbit]

Well, it's not going to give you max `date` by `hive` because you didn't specify which `hive` in the query.

[JulianMcCurdy]

makes sense...

would that be best done using another sub query or is there another more efficient way way to join 3 tables ?

[JulianMcCurdy]

if i do:

SELECT MAX(`Date`) strength FROM InspectionsNZ WHERE HiveID IN(SELECT Hives.ID FROM Hives LEFT JOIN Contacts ON Contacts.ContactID = Hives.ContactID WHERE RunGroup = '$run' GROUP BY Hives.ID)

i get single dates

but when i just run the sub query i do get all the hiveID's

SELECT Hives.ID strength FROM Hives LEFT JOIN Contacts ON Contacts.ContactID = Hives.ContactID WHERE RunGroup = '$run' GROUP BY Hives.ID

how would the query be structured ?

[Rabbit]

Well, you can get rid of the subquery and do a join instead. Try this:

SELECT ROUND(AVG(HiveStrength), 1) strength
 
FROM InspectionsNZ
   INNER JOIN (
      SELECT Hive, MAX(`Date`) AS MaxDate
      FROM InspectionsNZ
         LEFT JOIN Contacts
         ON InspectionsNZ.CustomerID = Contacts.ContactID
      WHERE RunGroup = '$run'
         AND Contacts.Status = 'Active'
         AND Type = 'Rental'
      GROUP BY Hive
   ) t
   ON InspectionsNZ.Hive = t.Hive
      AND InspectionsNZ.`Date` = t.MaxDate
 
   LEFT JOIN Contacts
   ON Contacts.ContactID = InspectionsNZ.CustomerID 
 
WHERE Contacts.RunGroup = '$run'
   AND Contacts.Status = 'Active'
   AND Type = 'Rental'

[JulianMcCurdy]

worked a treat, will take me a while to work out whats happening with that one but thank you so much for your help.

You are a legend!

[Rabbit]

Not a problem. Good luck.

Basically the inner join returns all the hives and their max date and joins it to the main table to filter down to just those records.