Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I'm trying to return a table with the depth of a node in a hierarchy represented using the nested set model, I'm following this tutorial but the query used in the section 'Finding the depth of Nodes' doesn't work for me: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

SELECT node.GroupName, (COUNT(parent.GroupName) - 1) AS depth
FROM CompanyGroup AS node,
        CompanyGroup AS parent
WHERE node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName
ORDER BY node.LeftID;

Running this query I get the error "Column 'CompanyGroup.GroupName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Can anyone explain why please?

Edit: wrong column in the error message, my apologies the error is: "Column "CompanyGroup.LeftID" is invalid..."

Answer 1

Try this one -

SELECT 
      node.GroupName
    , depth = COUNT(parent.GroupName) - 1
FROM CompanyGroup node
JOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName
ORDER BY MIN(node.LeftID) --<--

Or try this -

SELECT 
      node.GroupName
    , depth = COUNT(parent.GroupName) - 1
FROM CompanyGroup node
JOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName, node.LeftID
ORDER BY node.LeftID

Answer 2

Are you running a different query? You should not be receiving that particular error, but rather something like "Column "CompanyGroup.LeftID" is invalid in the ORDER BY..."

This query should work:

SELECT node.GroupName, (COUNT(parent.GroupName) - 1) AS depth
FROM CompanyGroup AS node,
        CompanyGroup AS parent
WHERE node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName
;

• SQL Fiddle Demo