Summary fields is another useful feature of the grid. The purpose of this feature is to display diffrent statistical information in the footer row of the grid. The default summary operation is the SUM (summary) SQL function, but you can use any other supported funciton like AVG (average), MIN, MAX, etc. There is no special variable in the grid that conrols this behavior. The summary array should be passed directly to the methods listed below with a parameter named $summary

The structure of the array is:

array("colname"=>"databaseexp")

or

array("colname"=>array("databaseexp"=>"operation"));

where

  • colname is the name corresponding to the name in colModel
  • databaseexp is either database field or database expresion like field1+field2
  • operation is the database statistical function like SUM, AVG, MIN, MAX, COUNT...

If "operation" is not explicitly used, we asumme that SUM function is used.

For example, if your SQL statement looks like this:

$grid->SelectCommand = "SELECT a, b , c+d AS e FROM table";

and you want the summary of c and d field to be displayed in the grid, the array should like this

$mysumarray = array('e'=>array("c+d"=>"SUM"));

Related methods
exportToExcel
queryGrid
editGrid
renderGrid

Let's ilustrate this again using this example.

Our goal is to display the average of the Freight field in the footer of the column:
The html code should be:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP jqGrid Class Example</title> 

<link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.7.1.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" />

<script src="js/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
jQuery(document).ready(function(){
....
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate"},
     {"name":"CustomerID","index":"CustomerID"},
     {"name":"Freight","index":"Freight"},
     {"name":"ShipName","index":"ShipName"}
],
"url":"querygrid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager",
"footerrow":true,
"userDataOnFooter": true
});
// Set navigator with search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});
......
});
</script>

</head>
<body>
......
<table id="grid"></table>
<div id="pager"></div>
.......
</body>
</html>

Note the two additional settings in the grid definition - footerrow and userDataOnFooter. The first setting enables the footer row the second uses the userData array to put the values on the footer row.

The PHP code should be:

<?php
require_once 'jq-config.php';
// include the jqGrid Class
require_once "php/jqGrid.php";
// include the PDO driver class
require_once "php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Create the jqGrid instance
$grid = new jqGrid($conn);
// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";

$avg = array('Freight'=>array('Freight'=>'AVG'));

// pass the avg array to the grid
$grid->queryGrid($avg);
?>