Works with postgres and mysql, not with mssql. I didnĀ“t work with mssql before.
Error
[Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ORDER BY a.lft asc [Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT * FROM ( SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed , ROW_NUMBER() OVER (ORDER BY a.lft asc) AS RowNumber FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ) A WHERE A.RowNumber BETWEEN 1 AND 20 [Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ORDER BY a.lft asc
Works with postgres and mysql, not with mssql. I didnĀ“t work with mssql before.
Error
[Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ORDER BY a.lft asc [Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT * FROM ( SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed , ROW_NUMBER() OVER (ORDER BY a.lft asc) AS RowNumber FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ) A WHERE A.RowNumber BETWEEN 1 AND 20 [Microsoft][SQL Server Native Client 11.0][SQL Server]Column '#__categories.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language,l.title AS language_title,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,COUNT(DISTINCT cp.id) AS count_published,COUNT(DISTINCT cu.id) AS count_unpublished,COUNT(DISTINCT ca.id) AS count_archived,COUNT(DISTINCT ct.id) AS count_trashed FROM #__categories AS a LEFT JOIN [#__languages] AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__content AS cp ON cp.catid = a.id AND cp.state = 1 LEFT JOIN #__content AS cu ON cu.catid = a.id AND cu.state = 0 LEFT JOIN #__content AS ca ON ca.catid = a.id AND ca.state = 2 LEFT JOIN #__content AS ct ON ct.catid = a.id AND ct.state = -2 WHERE a.extension = 'com_content' AND (a.published IN (0, 1)) GROUP BY a.id, l.title, uc.name, ag.title, ua.name ORDER BY a.lft asc
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8221.