Skip to main content

SQL - SubTotals

Using the Subtotal function allows you to break down the total of a numeric field into smaller sections, allowing you more detailed data to work with.

Using the SQL Wizard

To subtotal a column in the SQL Wizard start as usual by selecting the tables and fields you want to get information from on the first page of the SQL Wizard. Then highlight the fields you want to subtotal and click the No Total button and select Sum, Count, Absolute Count, or Average. The fields must be numeric fields and must be totaled in some fashion first for the subtotal function to work. The No Total button will remain grayed out for any non-numeric fields. Continue through the SQL Wizard to the Order Records Appear page. The SQL Wizard allows you to sort your query by four different fields. As long as one field in the query has been totaled. If a field has not been totaled the Subtotal on this Group will remain grayed out. Click the drop down box in the Sort results by section and all the selected fields will appear. Choose the filed you want to sort by. This is usually NOT the field that you are totalling. If you would like to insert a page break for each subtotal, check the Page Break on this Group check box. Complete the SQL Wizard and run the query.

Following the first example given below, from FOLIODT, select FOLIODT.NUMBER, FOLIODT.SUB, FOLIODT.TOTALENTRIES, and FOLIODT.BALANCE. Highlight FOLIODT.TOTALENTRIES, click the No Total button, and select Sum. Do the same for FOLIODT.BALANCE. Click Next until you reach the Order Records Appear page. Click on the drop down box and select FOLIODT.NUMBER. Check Subtotal on this Group. Click Finish. Running this query will return a report ordered by folio number subtotaling the number of transaction entries and the folio balance, by each folio number.

tip

Always total the fields you want to get a numeric result from and always order by the field you want to get a subtotal for.

Page Break on this Group​

This will insert a page break for each new value returned for the field to be sorted.

For example, from RESERVE, you could select CONFNUM, LASTNAME, ENTRYDATE, and STATE. Go to the Order records appear page and sort by STATE, then by ENTRYDATE. The report printed will list all reservations by state, then by the date entered. If you check the Page Break on this Group check box for sorting by STATE on the same query, each state would have its own page, with the reservations in entry date order. This can sometimes be helpful for reviewing non-numercal data.

Using Free-Form SQL Syntax

There are two ways to create Subtotals. The first (and easiest) is adding a SUBTOTAL .... ON .... clause to the end of the SQL statement. This option allows one level of Subtotals to be defined. The second option is using the SQL keywords GROUPING and ROLLUP which conforms to SQL/92 standards

Using SUBTOTAL ... [ON ... ]​

Note: This additional expression to the standard SQL "SELECT" clause is an InnQuest Software Vendor Extension, and is not valid SQL/92, SQL/99 supported.

After the entire SQL clause is written, the following additional query syntax can be added to the SQL statement to indicate which fields are sub-totalled and on what field or fields to determine the group break. If no "ON' expression is used, only Grand Totals will appear.

| SUBTOTAL | field name/column number of field to sum
AVG(field/column number) to compute average COUNT(field/column number) to count ACOUNT(field/column number) to count WAVG(field/column number : field to divide : field to divide by[%]) to compute weighted average MIN(field / column number) for the lowest value in the column MAX(field / column number) for the highest value in the column

[ON field name/column number of field which determine group-break {,...,...}]

By default, the "ON" occurs when a change occurs in the previous row. You can change this by adding either EXISTS( ), MONTH( ) or YEAR( ).

EXISTS(column): When the previous row changes from BLANK/NULL/ZERO to something else. In this case, there is only two sub-groups in the report, one for all the blank or null entries and one for the rest. This is helpful if you want a subtotal for everything without a date and then everything with a date.

MONTH(column): When the previous row changes from a different month (or year). This is ONLY valid for fields of type DATE. All others would produce unpredictable results.

YEAR(column): When the previous row changes from a different year. This is ONLY valid for fields of type DATE. All others would produce unpredictable results. | | ---- | ----|

For example, the following will sum both the TotalEntries and Balance columns when Number column changes:

SELECT Number,Sub,TotalEntries,Balance

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL TotalEntries,Balance ON Number

or sum columns 3 and 4 when column 1 changes:

SELECT Number as "Folio Number",Sub,TotalEntries,Balance

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL 3,4 ON 1

or just Grand Totals, only displaying the totals at the bottom with no Break Level:

SELECT Number,Sub,TotalEntries,Balance

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL TotalEntries, Balance

or sum everything with and without approval codes:

SELECT APPROVAL,TRANDATE, DEBIT,CREDIT

         FROM FOLIOTRN

         ORDER BY APPROVAL,TRANDATE

         SUBTOTAL DEBIT,CREDIT ON EXISTS(APPROVAL)

You can also qualify that the subtotal is an Average by using AVG(field or column number) rather than a Sum:

SELECT AuditDate,RoomRevenue,ADR

         FROM DAILYACT

         ORDER  BY AuditDate

         SUBTOTAL RoomRevenue, AVG(ADR)

The Average versus the Weighted Average (WAVG) is different where-as the average is the average of all entries in the column and the weighted average is a pure calculation of two other sub-totals to get the actual weighted average which in some cases is more precise. To get the weighted average, both columns which compute the weighted average must exist and be sub-totalled in the query.

Example: WAVG(Column to show total : column of subtotal to divide : column of the subtotal to divide by [% if show as a percentage])

SELECT foredate,rooms,checkins, '' as 'Average'

         FROM FORECAST

         ORDER  BY FOREDATE

         SUBTOTAL 2,3,WAVG(4:3:2) ON 1

OR to show as a percentage (multiple by 100) ...

SELECT foredate,rooms,checkins, '' as 'Average'

         FROM FORECAST

         ORDER  BY FOREDATE

         SUBTOTAL 2,3,WAVG(4:3:2%) ON 1

Click here for more information on Weighted Averages

You can also qualify that the subtotal is a Count by using either COUNT(field or column number) or ACOUNT(field or column number) rather than a Sum. ACOUNT (Absolute Count) is the same as Count, except ACOUNT will only count NON-BLANK and NON-ZERO rows.

SELECT number,sub,trantype,debit-credit,'' as '' from foliotrn

         ORDER BY number,sub,trannumber

         SUBTOTAL 4,COUNT(5) ON 1,2
 

SELECT number,sub,trantype,debit-credit,'' as '' from foliotrn

         ORDER BY number,sub,trannumber

         SUBTOTAL 4,ACOUNT(5) ON 1,2

Using ROLLUP​

The WITH ROLLUP modifier adds extra rows to the result set that represent super-aggregate summaries. The super-aggregated column is represented by a NULL value. Multiple aggregates over different columns will be added if there are multiple GROUP BY columns.

The LIMIT clause can be used at the same time, and is applied after the WITH ROLLUP rows have been added.

WITH ROLLUP cannot be used with ORDER BY. Some sorting is still possible by using ASC or DESC clauses with the GROUP BY column, although the super-aggregate rows will always be added last.

For example:

SELECT NUMBER, SUB, SUM(BALANCE) AS 'BALANCE'

FROM FOLIODT 

GROUP BY NUMBER,SUB WITH ROLLUP