Group by with out aggregate function

Question

Hi, The below query is working in the Mysql , how is it possible ? Can we expect the accurate result? Where this is completely wrong in SQL Server. Select col1,col2 from table group by col1

Brajesh Kumar · Answer

Hi sai In general, once you start GROUPing, every column listed in your SELECT must be either a column in your GROUP or some aggregate thereof. Let's say you have a table like this: | ID | Name | City | | 1 | Foo bar | San Jose | | 2 | Bar foo | San Jose | | 3 | Baz Foo | Santa Clara | If you wanted to get a list of all the cities in your database, and tried: SELECT * FROM table GROUP BY City ...that would fail, because you're asking for columns (ID and Name) that aren't in the GROUP BY clause. You could instead: SELECT City, count(City) as Cnt FROM table GROUP BY City ...and that would get you: | City | Cnt | | San Jose | 2 | | Santa Clara | 1 | ...but would NOT get you ID or Name. You can do more complicated things with e.g. subselects or self-joins, but basically what you're trying to do isn't possible as-stated. Break down your problem further (what do you want the data to look like?), and go from there. You need to apply aggregate function such as max(), avg() , count() in group by. Good luck!

Packiaraj Santhiyagu · Answer

Hi Sai, your query right in MySQL. But in Ms SQL server you need bring all the column in group by also same as select column. group by working on priority , first priority given to first column after group by key word.

Ravi Kiran Chanduri · Answer

Hello sai Reddy , I have seen the same kind of question in the following link and explained in detail. https://stackoverflow.com/questions/12843303/sql-vs-mysql-rules-about-aggregate-operations-and-group-by In a single shot, In My SQL fetching other columns form the given query is possible due to the setting of ONLY_FULL_GROUP_BY.

Mageshwaran R · Answer

Hi, Please refer the below link, it might helps you https://dba.stackexchange.com/questions/73442/group-by-clause-without-aggregate-function