SELECT id_rubro, rubro, name_e AS Empresas
FROM dbs_empresas, p_rubros
WHERE rubro_id = id_rubro AND activa_e='Y'
GROUP BY rubro
ORDER BY rubro
I get this error
Mens. 8120, Level 16, State 1, Line 1 Column 'e_rubros.id_rubro' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Although mysql allows you this query, it thinks it does not make much sense. Sql Server is more consistent and that's why it gives you the error you mention. You are asking that you group rows with the same item and at the same time you are not saying what to do with the fields id_rubro and name_e for each item group.
As the error tells you, you have two options:
-You add these columns to the group clause. With this you will have the grouping done first by item, then by id_rubro and then by name_e. If for each group these values are equal you will get the same answer as in mysql. If they are not, you will see that the query yields more results since subgroups are produced for each variation.
SELECT id_rubro, rubro, name_e AS Empresas
FROM dbs_empresas, p_rubros
WHERE rubro_id = id_rubro AND activa_e='Y'
GROUP BY rubro, id_rubro, name_e
ORDER BY rubro
-You specify an aggregate function so that you know how to group these values (for example, the MIN function that will select the minimum value of each grouping).
SELECT MIN(id_rubro), rubro, MIN(name_e) AS Empresas
FROM dbs_empresas, p_rubros
WHERE rubro_id = id_rubro AND activa_e='Y'
GROUP BY rubro
ORDER BY rubro