Delphi Report Builder For Grouping

The SQL 'GROUP BY' clause allows you to eliminate rows in your query where the field values repeat. For example, let's assume we have a database table that contains order records. Each order record has the customer number and the amount paid. If we viewed the data in this table, we would see that the value in the customer number field repeats where there are multiple orders for a customer.

We can use SQL to select data from the orders table and calculate the total amount paid for each customer. We can do this by specifying a group on the customer number field. By specifying the group, we are saying to the SQL engine: create one row in the result set for each customer number found. When the SQL engine runs the query, it will find multiple records for some customers; these records will be eliminated from the result set. SQL allows us to perform calculations on these repeated records and store the result in a new field of the result set.

These types of calculations can be created on the Calc tab of the Query Designer. Perform these steps in order to sum the amount paid for all customers in the orders table:

1 Click the 'Calc' icon to launch the Query Designer.

Query Designer Selection

2 Double-click the 'Amount Paid' field from the selection list at the top of the page. Amount Paid will be added to the list of calculations.

Create a Group Sum - cont.

3 Select 'Sum' as the function type for the calculation.

4 Enter the Field Alias you would like to use for this calculated field.

5 Click the SQL tab to make sure the generated SQL is valid.

6 Close the Query Designer and click the Preview icon to preview the data.

Tables | Fields Cales | Q.oup | Search 1 Soil 1 SQL 1

B Available Fields

Field Alias

Field SQL Alia:

| Table SQL Alia:

H

Customer No.

CUSTOMER. Cu...

CUSTOMER

1

Company

CUSTOMER.Co...

CUSTOMER

Address Line 1

CUSTOMER.Ad...

CUSTOMER

Address Line 2

CUSTOMER.Ad...

CUSTOMER

City

CUSTOMER. City

CUSTOMER

State

CUSTOMER.SI...

CUSTOMER

H

Zip Code

CUSTOMER.Zip

CUSTOMER

Calculations

Field Alias

Field SQL Alia:

Table SQL Alia: Function Expre:si

_îj

ITotal Amount Paic|

|SUM[0RDERS

CROERS Sum jj

Create a Group Sum - cont.

7 Check the data to make sure the sum is calculated as expected.

R Preview Data - Oideis

SUM CFAmountPaid

R Preview Data - Oideis

SUM CFAmountPaid

[j Action Club

1645

131,383.65

Action Divet Supply

3158

1536.80

Adventure Undersea

1884

174,705.05

American SCUBA Supply

3053

1183,084.40

Aquatic Drama

6312

117,814.00

Blue Glass Happiness

3884

14,517.70

Blue Jack Aqua Center

1380

116,612.50

Blue Spqrts

1563

1165,245.45

Blue Spqits Club

2118

180,832.20

Catamaran Dive Club

3054

152,703.55

Cayman Divers Wqrld Unlimited

1354

158,660.05

Central Underwater Supplies

5151

16,675.35

Davyjones1 Locker

2156

136,541.80

Divers of Blue-green

3041

181,633.85

Divers of Corlu, Inc.

2315

187,805.70

Divers of Venice

4312

16,300.00

Divers-for-Hire

5432

121,534.00

Fantastique Aquatica

1513

184,556.40

Fisherman's Eye

3151

112,022.00

Record 1 1

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Post a comment