MySQL Group By Modifier – WITH ROLLUP

The Group By WITH ROLLUP modifier in MySQL can give awesome flexibility in data warehouse kind of projects, where users are displayed different hierarchical inter related data and analytical flashy charts.

Always start with simple example : My project saves daily user visit data for my site. And it displays a table, which also can be displayed in flash charts as the data is present, showing page wise daily visit with daily, monthly aggregate of total visit while MySQL does the aggregation part for us. Something like

We have tow tables, ‘urls’ :

CREATE TABLE url (
 id int(11) NOT NULL AUTO_INCREMENT,
 url varchar(255) NOT NULL,
 page varchar(50) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO url (id, url, page) VALUES (NULL, 'index.php', 'Home Page'), (NULL, 'contact.php', 'Contact Us');

and a ‘daily_visit’ table :

CREATE TABLE  daily_visit (
id INT NOT NULL AUTO_INCREMENT ,
url_id INT NOT NULL ,
year INT NOT NULL ,
month VARCHAR( 20 ) NOT NULL ,
date VARCHAR( 10 ) NOT NULL ,
visit INT NOT NULL ,
PRIMARY KEY (  id )
) ENGINE = InnoDB ;

INSERT INTO daily_visit (id, url_id, year, month, date, visit) VALUES
(1, 1, 2009, 'August', '2009-08-10', 3),
(2, 1, 2009, 'September', '2009-09-08', 6),
(3, 1, 2009, 'August', '2009-08-15', 9),
(4, 1, 2009, 'September', '2009-09-22', 7),
(5, 2, 2009, 'February', '2009-02-06', 3),
(6, 2, 2009, 'February', '2009-02-24', 4),
(7, 2, 2009, 'April', '2009-04-04', 4),
(8, 2, 2009, 'April', '2009-04-14', 2);

Only one query does all for us. Just add the WITH ROLLUP clause with the Group By clause. Here is the whole script

<?php
$link = mysql_connect('localhost', 'root', '');
mysql_select_db('groupby_modifier', $link);

$sql = "SELECT	page, year, month, date, SUM(visit) as tot_visit
		FROM  daily_visit
			LEFT JOIN url ON url.id = daily_visit.url_id 
		GROUP BY page, year, month, date
		WITH ROLLUP";
$res = mysql_query($sql);
?>
<table width="45%" bgcolor="#CCCCCC">
	<tr>
		<td>
			<table cellspacing="1" cellpadding="1" width="100%">
				<tr>
					<th bgcolor="#FFFFFF">Page</th>
					<th bgcolor="#FFFFFF">Year</th>
					<th bgcolor="#FFFFFF">Month</th>
					<th bgcolor="#FFFFFF">Date</th>
					<th bgcolor="#FFFFFF">Visit</th>
				</tr>
				<?php while($row = mysql_fetch_assoc($res)){?>
					<tr>
						<td bgcolor="#FFFFFF" <?php if(!$row['page']) echo 'colspan="4"';?>><?php echo $row['page'] ? $row['page'] : '<strong style="color:red;">Total</strong>';?></td>
						<?php if($row['page']){?>
							<td bgcolor="#FFFFFF" <?php if(!$row['year']) echo 'colspan="3"';?>>
								<?php echo $row['year'] ? $row['year'] : '<strong style="color:red;">Total</strong>';?>
							</td>
						<?php }?>	
						<?php if($row['year']){?>
							<td bgcolor="#FFFFFF" <?php if(!$row['month']) echo 'colspan="2"';?>>
								<?php echo $row['year'] && !$row['month'] ? '<strong style="color:red;">Total</strong>' : $row['month'];?>
							</td>
						<?php }?>
						<?php if($row['month']){?>
							<td bgcolor="#FFFFFF">
								<?php echo $row['month'] && !$row['date'] ? '<strong style="color:red;">Total</strong>' : $row['date'];?>
							</td>
						<?php }?>
						<td bgcolor="<?php echo !$row['date'] ? '#FFFF8F' : '#FFFFFF';?>" ><?php echo $row['tot_visit'];?></td>
					</tr>
				<?php }?>
			</table>
		</td>
	</tr>
</table>

Now there is lots other things to keep in mind while using the WITH ROLLUP clause. Please refer MySQL Doc for more.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: