mySQL using GROUP BY and ORDER

[johngordon]

Not sure if this is possible, but I’ll try my best to explain…

I have a couple of tables, Lodges and Scores - one to many where any lodge can have multiple scores.

Lodges
LodgeID (PK)
Lodge
etc

Scores
ScoreID (PK)
LodgeID (FK)
Score
Comment

From this, I have a list of lodges using GROUP BY lodge, showing any lodge that has any scores, and the average score for that lodge.

This all works great - however, there is also a Comment field in the Scores table, and in this list I would like to include a link to the comments if the lodge has any records in the Scores table with a comment.

I have it working, but would like the link not to display if the lodge has not received any comments. Otherwise people see the link, click on it, and there might not be any comments.

So the problem, I assume, is because using GROUP BY, it may or may not happen to show a record from the scores table that includes a comment.

I have tried using ORDER BY as well, but that just orders the results. Or using WHERE, but that only returns the lodges that have had comments. I thought HAVING might be it, but that just filtered the results again.

What I am looking for is to use GROUP BY, but for the summarised lodges (rather than all results) to be order by Comment so has if the lodge has any comments, I can use that to show or hide the link to the comments for that lodge.

I hope that makes sense, and might be possible.

Thanks.

EDIT - I found this on Stack Overflow which is what I am trying to do, but suggests its not really possible?

http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause

[johngordon]

This is my original query:

SELECT scores.ScoreID, COUNT(scores.ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,   lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Comments, AY_finalist, AY_rank, 2016_Registered, Year, CategoryShort, CountryShort, Year
FROM lodges 
INNER JOIN continents ON lodges.ContinentID = continents.ContinentID 
INNER JOIN countries ON lodges.CountryID = countries.CountryID 
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID 
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID 
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID 
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 8 AND scores.CategoryID = 7 AND Year = '2016' AND Nominee = 'Y' 
GROUP BY Lodge

I have been trying sub queries, without any joy though:

SELECT * FROM
(SELECT scores.ScoreID, COUNT(scores.ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,   lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Comments, AY_finalist, AY_rank, 2016_Registered, Year, CategoryShort, CountryShort, Year
FROM lodges 
INNER JOIN continents ON lodges.ContinentID = continents.ContinentID 
INNER JOIN countries ON lodges.CountryID = countries.CountryID 
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID 
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID 
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID 
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 8 AND scores.CategoryID = 7 AND Year = '2016' AND Nominee = 'Y' 
ORDER BY Comments ASC)
AS Comments
GROUP BY Lodge

The above just lists one lodge with ScoreCount as the total number of all scores of all the lodges in that group.

If I change the * to the fields:

SELECT scores.ScoreID, COUNT(scores.ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,   lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Comments, AY_finalist, AY_rank, 2016_Registered, Year, CategoryShort, CountryShort, Year FROM
(SELECT scores.ScoreID, COUNT(scores.ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,   lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Comments, AY_finalist, AY_rank, 2016_Registered, Year, CategoryShort, CountryShort, Year
FROM lodges 
INNER JOIN continents ON lodges.ContinentID = continents.ContinentID 
INNER JOIN countries ON lodges.CountryID = countries.CountryID 
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID 
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID 
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID 
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 8 AND scores.CategoryID = 7 AND Year = '2016' AND Nominee = 'Y' 
ORDER BY Comments ASC)
AS Comments
GROUP BY Lodge

I get the error:

Unknown column ‘scores.ScoreID’ in ‘field list’

Am I barking up the right tree at all?

[r937]

[quote=“johngordon, post:2, topic:203142, full:true”]
This is my original query:

SELECT scores.ScoreID, COUNT(scores.ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,   lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Comments, AY_finalist, AY_rank, 2016_Registered, Year, CategoryShort, CountryShort, Year
FROM ...[/quote]

could you please identify which table the columns in your SELECT clause come from

i’m going to assume each lodge belongs to only one continent, and each lodge belongs to only one country…

… but could you please explain the relationships with nominations, categoriesfull, and categories short, so that we can get a sense of the one-to-many aspects

finally, with respect to your problem, what do you want should happen if there are multiple score comments for a given lodge? how does “a link to the comments” work if there’s more than one comment?

[johngordon]

Going back to the original, it would be:

SELECT 
scores.ScoreID, 
lodges.LodgeID,   
lodges.Lodge, 
countries.Country, 
lodges.CountryID, 
categoriesFull.CategoryID, 
nominations.NominationID, 
scores.Comments, 
nominations.AY_finalist, 
nominations.AY_rank, 
lodges.2016_Registered, 
nominations.Year, 
categoriesShort.CategoryShort, 
countries.CountryShort 
FROM 
lodges 
INNER JOIN continents 
ON lodges.ContinentID = continents.ContinentID 
INNER JOIN countries 
ON lodges.CountryID = countries.CountryID 
INNER JOIN scores 
ON lodges.LodgeID = scores.LodgeID 
INNER JOIN nominations 
ON lodges.LodgeID=nominations.LodgeID 
INNER JOIN categoriesFull 
ON nominations.CategoriesFullID = categoriesFull.CategoryID 
AND scores.categoryID = categoriesFull.CategoryID 
INNER JOIN categoriesShort 
ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE 
lodges.CountryID = 8
AND scores.CategoryID = 7
AND Year = '2016' 
AND Nominee = 'Y' 
GROUP BY 
Lodge

Yes - each lodge is one continent and one country - I only really have a continents and countries table to populate drop down boxes where the lodges are added, i.e. a parent child type thing where the options in the country drop down are dependent on the continent selected in the continent drop down.

Tables are:

lodges
LodgeID (PK)
Lodge
etc

continents
ContinentID (PK)

countries
CountryID (PK)
CountryContinentID (FK)
Country

categoriesFull
CategoryID (PK)
Category

nominations
NominationID (PK)
CategoriesFullID (FK)

scores
ScoreID (PK)
LodgeID (FK)
CategoryID (FK)
Score
Comments

It has gotten fairly complicated (for me at least), and it probably isn’t the best data structure, but it all seems to work.

With the link to the comments, that’s to another page with a query to show all the comments for the lodge that is clicked on.

So on this page its a list of all the lodges that have any scores, and I just want to use a bit of PHP like:

<?php  if ($row_rsLodges['Comments']<>""){ ?>LINK TO COMMENTS HERE<?php }  ?>

So really all I need to do is ORDER the results before the GROUP BY, but SQL doesn’t supper that as far as I can tell.

As it stands, some results show the link (presumably because the record elected by the GROUP BY for that lodge is one with a comment), but others do not, because the record elected by the GROUP BY for that lodge is one without any comments.

So the theory is, if the results are somehow ordered by Comment first, the record shown by the GROUP BY will always have that Comment field hook to correctly display the link when required, but hide it if the lodge has no scores with a comment.

Sorry its so complicated sounding, but hope it makes sense.

Appreciate you having a look.

[johngordon]

Actually, let me simplify it a bit first…

Probably only need to look at two tables - lodges and scores - to best explain it without overcomplicating it.

table - lodges
LodgeID (PK)
Lodge etc

table - scores
ScoreID (PK)
LodgeID (FK)
Score
Comment

Any lodge can have multiple Scores, which may or may not contain a Comment. Its an online voting thing where people leave a score out of 10, with the option of leaving a comment.

So the simple query of:

SELECT 
scores.ScoreID, 
lodges.LodgeID,   
lodges.Lodge, 
scores.Comment
FROM 
lodges 
INNER JOIN scores 
ON lodges.LodgeID = scores.LodgeID 

Might return something like:

ScoreID, LodgeID, Lodge, Comment
1, 1, Lodge A, Great lodge
2, 1, Lodge A
3, 2, Lodge B
4, 2, Lodge B, Best lodge ever
5, 3, Lodge C
6, 3, Lodge C

Where Lodges A and B have any comments, but Lodge C does not.

If I add a GROUP BY:

SELECT 
scores.ScoreID, 
lodges.LodgeID,   
lodges.Lodge, 
scores.Comments
FROM 
lodges 
INNER JOIN scores 
ON lodges.LodgeID = scores.LodgeID 
GROUP BY  
Lodge

The GROUP BY might elect the first instance of each lodge,and return:

ScoreID, LodgeID, Lodge, Comment
1, 1, Lodge A, Great lodge
3, 2, Lodge B
5, 3, Lodge C

Where there is the Comment field I need for Lodge A, i.e. it meets the <> “” criteria, and I can display the link.

But shows ScoreID 3 for Lodge B, and does not meet the <> “” criteria, so the link doesn’t get displayed, even though Lodge B has some scores with a comment (ScoreID 4).

So I guess the object is to somehow order the results before the GROUP BY, by comment to get:

ScoreID, LodgeID, Lodge, Comment
1, 1, Lodge A, Great lodge
4, 2, Lodge B, Best lodge ever
2, 1, Lodge A
3, 2, Lodge B
5, 3, Lodge C
6, 3, Lodge C

So that the GROUP BY becomes:

ScoreID, LodgeID, Lodge, Comment
1, 1, Lodge A, Great lodge
4, 2, Lodge B, Best lodge ever
5, 3, Lodge C

Hope that makes more sense.

Thanks again.

[r937]

here’s the thing about GROUP BY – it “collapses” detail rows into an aggregate row, so the actual detail rows that are produced by the FROM clause are ~not~ available to be returned… only aggregate rows are returned

so if you GROUP BY lodge, then wherever there’s a one-to-many relationship, like with scores, you really shouldn’t have a column from the “many” side in the SELECT clause, because it will be indeterminate (and there’s no use hoping that ORDER BY will get you out of that mess)

which is why i asked about the other relationships

it appears that nominations has a lodgeID (you didn’t show this) and the relatiosnhip to categoriesFull depends on which score it is…

… so that whole business with categories is indeterminate too

here at least is the solution to your simplified issue –

SELECT lodges.LodgeID , lodges.Lodge , lodges.Country , lodges.CountryID , COUNT(scores.ScoreID) as scoreCount , AVG(Score) as AverageScore , MAX(scores.Comments) AS max_comment FROM lodges INNER JOIN scores ON scores.LodgeID = lodges.LodgeID GROUP BY lodges.LodgeID
with this query, the “max_comment” column can be used to determine if there were any comments

i’m sorry about the other tables, but i don’t think the relationships are clear enough for me to work them properly into the query

[johngordon]

Thanks - that makes sense why ORDER BY won’t work, even though its kind of what is required from looking at it.

Looks like the whole thing with sub queries wasn’t the solution here, the MAX function has solved it, and it is now working the way I intended.

Thanks again for taking a look and helping out.