Reference - Data handling - group

<p>where: {offices where="country = 'USA'", order='officeCode'}{$officeCode},{/offices}</p>
<p>distinct: {offices distinct, fields='country', order='country'}{$country},{/offices}</p>
<p>distinctrow: {offices distinctrow, fields='country', order='country'}{$country},{/offices}</p>
<p>group: {payments fields='customerNumber, count(*) as cnt', group='customerNumber', having='cnt > 9', order='customerNumber'}{$customerNumber}={$cnt},{/payments}</p>
<p>rollup: {offices key='', fields='count(*) as cnt', group='country', rollup}{last@offices}{$cnt}{/last@offices}{/offices}</p>
<p>db: {sales db='employees', where="jobTitle = 'President'"}{$lastName}{/sales}</p>
<p>key: {offices key='city', where="country = 'USA'"}{$city},{/offices}</p>
<p>union: {offices where="officeCode = 1", union='offices'}{$officeCode},{/offices}</p>
<p>join: {employees join='offices on employees.officeCode = offices.officeCode', where="offices.city = 'Boston'", order='lastName'}{$lastName},{/employees}</p>
<p>htmlAttrJson: {select:offices htmlAttrJson='record', fields='officeCode', where="officeCode = 1"}</p>

{data 'orders'}
  [ {"customer":"Ann","number":101,"total":40}, {"customer":"Bob","number":102,"total":15},
    {"customer":"Ann","number":103,"total":25}, {"customer":"Cleo","number":104,"total":60},
    {"customer":"Bob","number":105,"total":35} ]
{/data}

{orders group='customer', sum='total'}
  <h4>{$customer}: {$count} orders, {$total}</h4>
  {rows}
    <p>Order {$number}: {$total}</p>
  {/rows}
{/orders}

{data 'orders'}
  [ {"customer":"Ann","number":101,"total":40}, {"customer":"Bob","number":102,"total":15},
    {"customer":"Ann","number":103,"total":25}, {"customer":"Cleo","number":104,"total":60},
    {"customer":"Bob","number":105,"total":35} ]
{/data}

<table>
  <tr><th>Customer</th><th>Orders</th><th>Total</th><th>Average</th><th>Smallest</th><th>Largest</th></tr>
  {orders group='customer', sum='total', avg='total', min='total', max='total', sort='total DESC'}
    <tr>
      <td>{$customer}</td><td>{$count}</td><td>{$total}</td>
      <td>{$avg_total}</td><td>{$min_total}</td><td>{$max_total}</td>
    </tr>
  {/orders}
</table>

{data 'rows'}[{"name":"b"},{"name":"a"}]{/data}{rows group='nmae'}{$count}{/rows}

{data 'xs'}[{"count":2},{"count":3}]{/data}{xs group='count'}{$count}{/xs}

{data 'pg'}[{"n":"a","v":1},{"n":"b","v":2}]{/data}{pg group='n', sum}{$v}{/pg}

{data 'pg'}[{"n":"a","v":1},{"n":"b","v":2}]{/data}{pg group}{$n}{/pg}

{data 'xs'}[{"t":"a","rows":5,"count":2,"n":1},{"t":"a","rows":7,"count":3,"n":2},{"t":"b","rows":1,"count":1,"n":3}]{/data}
[{xs group='t', sum='rows, count'}{$t}:{$count}:{$sum_rows}:{$sum_count}:{rows}{$n}{/rows}|{/xs}]

<?php

  $orders = [
    [ "number" => 1, "customer" => "bob",   "total" => "10.10" ],
    [ "number" => 2, "customer" => "alice", "total" => 20      ],
    [ "number" => 3, "customer" => "bob",   "total" => "20.20" ],
    [ "number" => 4, "customer" => "alice", "total" => 7       ],
    [ "number" => 5, "customer" => "carol", "total" => 1       ]
  ];

?>

{orders group='customer'}[{$customer} {$count}:{rows}{$number}{/rows}]{/orders}

<?php

  $orders = [
    [ "number" => 1, "customer" => "bob",   "total" => "10.10" ],
    [ "number" => 2, "customer" => "alice", "total" => 20      ],
    [ "number" => 3, "customer" => "bob",   "total" => "20.20" ],
    [ "number" => 4, "customer" => "alice", "total" => 7       ],
    [ "number" => 5, "customer" => "carol", "total" => 1       ]
  ];

?>

{orders group='customer', avg='total', sum='total', min='total', max='total, number'}[{$customer} {$total} {$sum_total} {$avg_total} {$min_total} {$max_total} {$number}]{/orders}

<?php

  $orders = [
    [ "number" => 1, "customer" => "bob",   "total" => "10.10" ],
    [ "number" => 2, "customer" => "alice", "total" => 20      ],
    [ "number" => 3, "customer" => "bob",   "total" => "20.20" ],
    [ "number" => 4, "customer" => "alice", "total" => 7       ],
    [ "number" => 5, "customer" => "carol", "total" => 1       ]
  ];

?>

{orders sort='total', group='customer'}[{$customer}:{rows}{$number}{/rows}]{/orders}|{orders group='customer', sum='total', sort='total DESC', first=2}[{$customer} {$total}]{/orders}|{orders group='customer', where='$count gt 1', negative}{$customer}{/orders}

<?php

  // A float below 0.0001 is written in E notation, 1.0E-5, so its decimals were never
  // counted and the sum was rounded to the one decimal of 0.5.

  $tiny = [ [ 'g' => 'a', 'x' => 0.00001 ], [ 'g' => 'a', 'x' => 0.5 ] ];

?>

{tiny group="g", sum="x"}{$x}{/tiny}

<?php

  $orders = [
    [ "number" => 1, "customer" => "bob",   "total" => "10.10" ],
    [ "number" => 2, "customer" => "alice", "total" => 20      ],
    [ "number" => 3, "customer" => "bob",   "total" => "20.20" ],
    [ "number" => 4, "customer" => "alice", "total" => 7       ],
    [ "number" => 5, "customer" => "carol", "total" => 1       ]
  ];

?>

{orders group='customer', sum='total'}[{$customer} {$total}:{rows} {$total}{/rows}]{/orders}

<?php

  // The same customer number as an integer and as text - a database row beside a CSV row -
  // is one value to group on, as it is one value to dedup.

  $mixed = [ [ 'c' => 5, 'v' => 1 ], [ 'c' => '5', 'v' => 2 ], [ 'c' => 6, 'v' => 3 ] ];

?>

{mixed group="c"}[{$c}:{$count}]{/mixed}

<?php

  // A sort before first= and one after it, a sort before a group and one after it: each
  // sorts on its own value - both sorted on the last one written.

  $xs     = [ [ 'n' => 3 ], [ 'n' => 1 ], [ 'n' => 2 ], [ 'n' => 5 ], [ 'n' => 4 ] ];
  $orders = [ [ 'c' => 'a', 't' => 1, 'd' => '2026-03' ], [ 'c' => 'b', 't' => 9, 'd' => '2026-01' ], [ 'c' => 'a', 't' => 5, 'd' => '2026-02' ],
              [ 'c' => 'a', 't' => 2, 'd' => '2026-01' ], [ 'c' => 'b', 't' => 1, 'd' => '2026-02' ] ];

?>

[{xs sort='n', first=2, sort='n DESC'}{$n}{/xs}] {orders sort='d', group='c', sum='t', sort='t DESC'}[{$c}:{$t}:{rows}{$d}/{$t},{/rows}]{/orders}