Grouping Data

Some information is better viewed in summary form than in detail. For instance, usually it is more important to know the total amount of cash received for the day than reviewing each cash transaction. Group by is an SQL clause that allows you to summarize the information you are reviewing either by a total, average, or the minimum or maximum value or the data selected.

Using the SQL Wizard

To summarize data using the SQL Wizard, start by selecting the field with the information to be summarized. In other words, to find a total amount for a account code, select the debit or credit field, depending on the account code, to summarize. Do that by highlighting the field to be selected from the list of fields available and click the SUM button. The field will move to the selected fields pane with SUM preceding it and the selected field name in parentheses. Select the other fields you want on the report and proceed through the wizard.

If you would like to summarize the count, the number of times the transaction occurred in the data, use the drop down box next to the SUM button and select Count when selecting the field. Average will summarize by the average of each transaction. Minimum and Maximum will summarize by the minimum and maximum value in the field respectively.

Clicking on Custom Formula allows you to create a formula of multiple fields and then summarize by Sum, Count, Average, Minimum or Maximum.

For example, if you wanted to know the highest amount, the maximum, posted for each account code for a specific audit date, from the JOURNAL table you would select TRANADATE and TRANTYPE. Then highlight DEBIT and click on the drop down box and select Custom Formula. A new window will appear and in the bottom pane JOURNAL.DEBIT will appear. Click the Add Operator button and choose + (Plus) to be able to add DEBIT to another field. Go to the upper pane and expand the Journal table. Select CREDIT and JOURNAL.CREDIT will appear in the bottom pane. Use the radio buttons to choose how to summarize the data. In our example, we would click Max to sort out the maximum amount. The results returned would list each account code that was posted for that audit date and the highest amount, debit or credit, for each account code. Click Finish and run the query.

To change from getting the maximum amount to the minimum, open the formulated field and select the Min radio button.

Using Free-Form SQL Syntax

The Group by clause requires that one of the fields be summarized using a function, usually either SUM, COUNT, AVG, MIN or MAX, and then each other field included in the report is listed by field after the Group By clause. The Order by clause is also required to define the order in which the data will be organized.

SELECT Field1, Field2, FUNCTION(Field3)

FROM Table

GROUP BY Field1, Field2

ORDER BY Field1, Field2

In this query example, the total amount posted for account codes 100, 101 and 102 will be returned in audit date, the account code order.

SELECT JOURNAL.TRANADATE ,JOURNAL.TRANTYPE , SUM(JOURNAL.DEBIT) 

 FROM JOURNAL

 WHERE JOURNAL.TRANTYPE = 100 OR JOURNAL.TRANTYPE = 101 OR JOURNAL.TRANTYPE = 102

GROUP BY JOURNAL.TRANADATE,JOURNAL.TRANTYPE

 ORDER BY JOURNAL.TRANADATE,JOURNAL.TRANTYPE

Note the Group By and Order By clauses come after the SQL statement, but would come before a SubTotal request.