PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

5 Jan 2014

Group by in mysql table

                       If your column in mysql table contains similar values, then you will use 'GROUP BY' mysql command to display unique values. It is used to eliminate the duplicate values in mysql table. It takes only  first row value from top to bottom in mysql table. The mysql query as follows as:

                       SELECT * FROM table_name GROUP BY field_name


Group by in Mysql:


                                      Consider the following table. table1 is name of this table.

group by in mysql

                              Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

                       SELECT * FROM table1 GROUP BY id 

Now you'll get output like this:

display unique values using group by in mysql


                           You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.


Group by multiple columns in mysql:


                           You can group the multiple columns in single mysql query.  The mysql query as follows as:

                       SELECT * FROM table1 GROUP BY id , count 

             Where,
                        - The column 'id' got the first preference to group.
                        - Then the column 'count' is grouped.
Your output like this:

group by multiple columns in mysql

                        You can see the mysql table 'table1' which is grouped by both id and count. But you can't the get unique values in both mysql columns.

Related Post: