Group by with out aggregate function

Hi,

Please refer the below link, it might helps you

 

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.  

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!