KiPiPo.com blog gadget design wallpaper web programming review

7Nov/100

the various uses of “group by” in mysql


I will explain some useful tips  using "group by" query in mysql.

First create new table for testing in your database ...

CREATE TABLE `movie` (
`id` tinyint(4) NOT NULL auto_increment,
`title` varchar(100) default NULL,
`genre` varchar(100) default NULL,
`price` double(10,2) default NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `movie` (`id`, `title`, `genre`, `price`) VALUES (1, 'Spiderman', 'action', 5.00);
INSERT INTO `movie` (`id`, `title`, `genre`, `price`) VALUES (2, 'Hulk', 'action', 4.00);
INSERT INTO `movie` (`id`, `title`, `genre`, `price`) VALUES (3, 'Romance in the rain', 'romance', 4.00);
INSERT INTO `movie` (`id`, `title`, `genre`, `price`) VALUES (4, 'Hello World', 'comedy', 10.00);
INSERT INTO `movie` (`id`, `title`, `genre`, `price`) VALUES (5, 'Bang Bang', 'comedy', 6.00);

Calculate average price all items

select avg(price) from movie;

Calculate average price for each genre

select genre,avg(price)as avg_price from movie
group by genre
genre avg_price
action 4.500000
comedy 8.000000
romance 4.000000

Count how many movie for each genre

select genre,count(title) as total_movie from movie
group by genre
genre total_movie
action 2
comedy 2
romance 1

Get Movie list of each genre using "group_concat"

select genre,group_concat(distinct title) as movie_list from movie
group by genre
genre movie_list
action1 Spiderman,Hulk
comedy Hello World,Bang Bang
romance Romance in the rain

use "dintinct" inside group_concat if want to remove duplicate.

Related posts:

create simple menu navigation using CSS
basic jquery tutorial
check checkbox with specific value ( using jQuery )
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

 

No trackbacks yet.