By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,651 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,651 IT Pros & Developers. It's quick & easy.

Average on MAX by Group

P: 12
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. ROUND(AVG(HiveStrength), 1) strength 
  3. FROM 
  4. InspectionsNZ
  5. LEFT JOIN Contacts ON Contacts.ContactID = InspectionsNZ.CustomerID 
  6. WHERE 
  7. Contacts.RunGroup = '$run'
  8. AND
  9. 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')
  10. AND
  11. Contacts.Status = 'Active'
  12. AND 
  13. 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
Feb 2 '12 #1

✓ answered by Rabbit

Well, you can get rid of the subquery and do a join instead. Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ROUND(AVG(HiveStrength), 1) strength
  2.  
  3. FROM InspectionsNZ
  4.    INNER JOIN (
  5.       SELECT Hive, MAX(`Date`) AS MaxDate
  6.       FROM InspectionsNZ
  7.          LEFT JOIN Contacts
  8.          ON InspectionsNZ.CustomerID = Contacts.ContactID
  9.       WHERE RunGroup = '$run'
  10.          AND Contacts.Status = 'Active'
  11.          AND Type = 'Rental'
  12.       GROUP BY Hive
  13.    ) t
  14.    ON InspectionsNZ.Hive = t.Hive
  15.       AND InspectionsNZ.`Date` = t.MaxDate
  16.  
  17.    LEFT JOIN Contacts
  18.    ON Contacts.ContactID = InspectionsNZ.CustomerID 
  19.  
  20. WHERE Contacts.RunGroup = '$run'
  21.    AND Contacts.Status = 'Active'
  22.    AND Type = 'Rental'

Share this Question
Share on Google+
20 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Can you post some sample data and results?
Feb 3 '12 #2

P: 12
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
Feb 3 '12 #3

Rabbit
Expert Mod 10K+
P: 12,365
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??
Feb 3 '12 #4

P: 12
you mean take out the MAX from the sub query?

Also, would it matter if "hiveStrength' was 0 ?
Feb 3 '12 #5

P: 12
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
Feb 3 '12 #6

P: 12
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?
Feb 3 '12 #7

Rabbit
Expert Mod 10K+
P: 12,365
So when you took out the average, it was returning the correct records? Or is it returning the wrong records?
Feb 3 '12 #8

P: 12
wrong,

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

what is getting is 0 0
Feb 3 '12 #9

Rabbit
Expert Mod 10K+
P: 12,365
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.
Feb 3 '12 #10

P: 12
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
Feb 3 '12 #11

Rabbit
Expert Mod 10K+
P: 12,365
Oh mySQL, I believe they use ` ` to demarcate a field.
Feb 3 '12 #12

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

Thanks for your help...
Feb 3 '12 #13

Rabbit
Expert Mod 10K+
P: 12,365
Run just the subquery and see if it returns the correct date. And use `Date`
Feb 3 '12 #14

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



Expand|Select|Wrap|Line Numbers
  1. 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
Feb 3 '12 #15

Rabbit
Expert Mod 10K+
P: 12,365
Well, it's not going to give you max `date` by `hive` because you didn't specify which `hive` in the query.
Feb 3 '12 #16

P: 12
makes sense...

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

P: 12
if i do:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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 ?
Feb 3 '12 #18

Rabbit
Expert Mod 10K+
P: 12,365
Well, you can get rid of the subquery and do a join instead. Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ROUND(AVG(HiveStrength), 1) strength
  2.  
  3. FROM InspectionsNZ
  4.    INNER JOIN (
  5.       SELECT Hive, MAX(`Date`) AS MaxDate
  6.       FROM InspectionsNZ
  7.          LEFT JOIN Contacts
  8.          ON InspectionsNZ.CustomerID = Contacts.ContactID
  9.       WHERE RunGroup = '$run'
  10.          AND Contacts.Status = 'Active'
  11.          AND Type = 'Rental'
  12.       GROUP BY Hive
  13.    ) t
  14.    ON InspectionsNZ.Hive = t.Hive
  15.       AND InspectionsNZ.`Date` = t.MaxDate
  16.  
  17.    LEFT JOIN Contacts
  18.    ON Contacts.ContactID = InspectionsNZ.CustomerID 
  19.  
  20. WHERE Contacts.RunGroup = '$run'
  21.    AND Contacts.Status = 'Active'
  22.    AND Type = 'Rental'
Feb 4 '12 #19

P: 12
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!
Feb 4 '12 #20

Rabbit
Expert Mod 10K+
P: 12,365
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.
Feb 4 '12 #21

Post your reply

Sign in to post your reply or Sign up for a free account.