Commands

With aggregate functions, you can calculate the sum, minimum, maximum value, and count of individual rows for a column in the result set. If the result set includes header levels, the calculation is also performed for each break level.

dbr.summary.options include:

We'll calculate a summary for Items and the Weight column and also the maximum for Weight

select 'dbr.sum', 'Items', 'Weight'; /* Calculate sum from Items and Weight column */
select 'dbr.max', 'Weight';    /* Calculate sum from Items and Weight column */
select 'dbr.count', 'Title';

select Title, sum(Items), sum(Weight) as 'Weight'
from mydb.Exampledata
group by Title;

This will produce the following result:

If an header level is included, the subtotals are also calculated:

select 'dbr.hdr', 'Year';    /* Sets the Year to be a header column */
select 'dbr.sum', 'Items', 'Weight'; /* Calculate sum from Items and Weight column */
select 'dbr.max', 'Weight';    /* Calculate sum from Items and Weight column */
select 'dbr.count', 'Title';  /* How many rows do we have? */
select 'dbr.summary.text', 'Year', 'Total';

select Year, Title, Items, Weight
from mydb.Exampledata
order by Year, Title;

Subtotals are calculated and the text appears in the summary row:

The dbr.calc command allows the insertion of formulas for a column. References to other columns are made using the Column reference. You can freely add any complex formula using PHP syntax.

In addition to column references, one can use references to aggregate values (horizontal and vertical) by using syntax [column.sum] for vertical aggregate values and [column.hsum] for horizontal aggregate values (crosstab values). This syntax can be used for all aggregates (sum, min, max, count, avg).

When performing calculations, the formula is applied to all columns that are not null.

In the example, if we want to calculate the percentage based on two columns, we reserve a place for the calculation by including a null value in the query and assigning it a column reference (e.g., "percent").

select 'dbr.sum', 'ok', 'all';
select 'dbr.calc', 'percent', '[ok]/[all]*100';

select Group,
       OK[ok],
       allresults as 'All[all]',
       null as '%[percent]'
from group_sessions;

When a calculation formula is applied to the crosstable data value, it is performed for each dataset.

select 'dbr.sum', 'ok', 'all';
select 'dbr.hsum', 'ok', 'all';
select 'dbr.hdr', 'group';
select 'dbr.crosstab', 'session';
select 'dbr.colstyle', 'percent', '%.2f %';
select 'dbr.summary.text', 'group', 'Total';
select 'dbr.calc', 'percent', '[ok]/[all]*100';

select Group[group],
       Session[session],
       OK[ok],
       allresults as 'All[all]',
       null as '%[percent]'
from group_sessions;

An aggregate column reference can refer to the aggregate value for the column. A normal use case is where we need to calculate a percent share of each column of the total value.

The [Items.sum] reference in the formula refers to the total sum of the Items column.

select 'dbr.subtitle', 'Vertical summary';
select 'dbr.calc', 'percent', '[Items]/[Items.sum]*100';
select 'dbr.colstyle', 'percent', '%.0f %';
select 'dbr.sum', 'Items';

select   Name, 
  sum(Items) as 'Items',
  null as '%[percent]'
from mydata
group by Name;

A horizontal aggregate column reference performs calculations over the data columns in a crosstable. In this example, we'll calculate the share for each quarter of the total year for each row.

The [Items] refers to each items column (each quarter). The [Items.hsum] refers to the total column and is independent of the actual Total column generated by the dbr.hsum command. The dbr.hnull command generates a placeholder (a null column) for the percent-column.

select 'dbr.subtitle', 'Horizontal summary (Items.hsum)';
select 'dbr.crosstab', 'quarter';
select 'dbr.calc', 'percent', '[Items]/[Items.hsum]*100';
select 'dbr.hnull', 'percent';
select 'dbr.colstyle', 'percent', '[color:#888]%.0f %';
select 'dbr.sum', 'Items';
select 'dbr.hsum', 'Items';

select   
  Name, 
  f_quarter(thedate) as 'Quarter[quarter]', 
  sum(Items) as 'Items',
  null as '%[percent]'
from mydata
group by Name, 2;

The following example combines both calculation methods. The result differs from the earlier example in that the total percent column will show the percentage of each row's total value from the grand total.

To achieve this, we'll use the dbr.hnull command to create a placeholder for the row total.

select 'dbr.subtitle', 'Horizontal summary (Items.hsum + Items.sum)';
select 'dbr.crosstab', 'quarter';

/* Calculate percent for data columns */
select 'dbr.calc', 'data_percent', '[Items]/[Items.hsum]*100';

/* Create a placeholder null-column for the total_percent */
select 'dbr.hnull', 'total_percent';

/* Calculate the total_percent */
select 'dbr.calc', 'total_percent', '[Items]/[Items.sum]*100';

/* We do not need to show the row percentage for each data column, only in total column */
select 'dbr.hidecolumn.data', 'total_percent';

/* Fade the percent columns a bit to make table more readable */
select 'dbr.colstyle', 'data_percent', '[color:#888]%.0f %';
select 'dbr.colstyle', 'total_percent', '[color:#888]%.0f %';
select 'dbr.sum', 'Items';
select 'dbr.hsum', 'Items';

select   Name, 
  f_quarter(thedate) as 'Quarter[quarter]', 
  sum(Items) as 'Items',
  null as '%[data_percent]',
  null as 'Total %[total_percent]'
from mydata
group by Name, 2;