970_90

Friday, 19 October 2012

Mysql-Group-Concat

Group_concat: its used to concatenate column values into a single string.


Create a table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Following are examples

select group_concat(name) as all_user where country='india'
List all the name separated by comma (,) 
for example: arun,raj,suman,arun,ansh


select group_concat(name SEPARATOR '--') as all_user where country='india'
List all the name separated by double dash(--)
for example: arun--raj--suman--arun--ansh


select group_concat(name order by name asc) as all_user where country='india'
List all the name separated by comma (,) but order by name 
for example: arun,arun,raj,suman,ansh


select group_concat(distinct name) as all_user where country='india'
List all the name separated by comma (,) but with distinc name only
for example: arun,raj,suman,arun,ansh

1 comment:

  1. Example of Mysql Sum Function

    (sum(avg_max_temp_f * month ))/10

    ReplyDelete