User Profile

JulianMcCurdy · Feb 4 '12, 01:58 AM

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!

JulianMcCurdy · Feb 3 '12, 09:32 PM

if i do:

Code:

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

Code:

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

...

JulianMcCurdy · Feb 3 '12, 09:24 PM

makes sense...

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

JulianMcCurdy · Feb 3 '12, 08:28 PM

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

Code:

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

JulianMcCurdy · Feb 3 '12, 10:58 AM

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

Thanks for your help...

JulianMcCurdy · Feb 3 '12, 04:59 AM

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.C ustomerID = Con' at line 9

JulianMcCurdy · Feb 3 '12, 04:51 AM

wrong,

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

what is getting is 0 0

JulianMcCurdy · Feb 3 '12, 04:06 AM

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?

JulianMcCurdy · Feb 3 '12, 03:42 AM

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 · Feb 3 '12, 03:36 AM

you mean take out the MAX from the sub query?

Also, would it matter if "hiveStreng th' was 0 ?

JulianMcCurdy · Feb 3 '12, 01:30 AM

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

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Average on MAX by Group