SQL statement-select (7): AVG (), sum (), max (), min (), count ()

Group

Group by can also be grouped by multiple fields (","), but in this example, only the continent field is suitable for grouping,
Only the area and population fields are suitable for statistics.

In this example:

Code file:

Unit unit1; interfaceuses windows, messages, sysutils, variants, classes, graphics, controls, forms, dialogs, stdctrls, extctrls, grids, dbgrids, DB, ADODB; type tform1 = Class (tform) dbgrid1: TDBGrid; performance1: tdatasource; usage: Unknown; Panel1: tpanel; button1: tbutton; button2: tbutton; button3: tbutton; button4: tbutton; button5: tbutton; Procedure upload (Sender: tobject); Procedure button1click (Sender: tobject); Procedure button2click (Sender: tobject); Procedure button3click (Sender: tobject ); procedure button4click (Sender: tobject); Procedure button5click (Sender: tobject); end; var form1: tform1; implementation {$ R *. DFM} procedure tform1.button1click (Sender: tobject); begin with adodataset1 do begin close; commandtext: = 'select continent, '+ 'avg (area) as average area, AVG (Population) as average population '+' from country group by Continent'; open; end; Procedure tform1.button2click (Sender: tobject); begin with adodataset1 do begin close; commandtext: = 'select continent, '+ 'sum (area) as total area, sum (Population) as total population' + 'from country group by continent'; open; end; procedure tform1.button3click (Sender: tobject); begin with adodataset1 do begin close; commandtext: = 'select continent, '+' max (area) as the largest area, min (Population) as minimum population '+' from country group by Continent'; open; end; Procedure tform1.button4click (Sender: tobject); begin with adodataset1 do begin close; commandtext: = 'select continent, '+' max (area + population) as has the largest area and total population, '+' min (area + population) as area and population minimum '+' from country group by Continent'; open; end; Procedure tform1.button5click (Sender: tobject); begin with adodataset1 do begin close; commandtext: = 'select count (*) as total records from country '; open; end; Procedure tform1.formcreate (Sender: tobject); var mdbfile: string; begin mdbfile: = getenvironmentvariable ('commonprogramfiles'); mdbfile: = mdbfile + '\ codegear shared \ data \ dbdemos. MDB '; adodataset1.connectionstring: = 'provider = Microsoft. jet. oledb.4.0; Data Source = '+ mdbfile +'; persist Security info = false'; dbgrid1.datasource: = performance1; performance1.dataset: = adodataset1; end.
 

Form file:

object Form1: TForm1  Left = 0  Top = 0  Caption = 'Form1'  ClientHeight = 407  ClientWidth = 626  Color = clBtnFace  Font.Charset = DEFAULT_CHARSET  Font.Color = clWindowText  Font.Height = -11  Font.Name = 'Tahoma'  Font.Style = []  OldCreateOrder = False  OnCreate = FormCreate  PixelsPerInch = 96  TextHeight = 13  object DBGrid1: TDBGrid    Left = 0    Top = 33    Width = 626    Height = 374    Align = alClient    DataSource = DataSource1    TabOrder = 0    TitleFont.Charset = DEFAULT_CHARSET    TitleFont.Color = clWindowText    TitleFont.Height = -11    TitleFont.Name = 'Tahoma'    TitleFont.Style = []  end  object Panel1: TPanel    Left = 0    Top = 0    Width = 626    Height = 33    Align = alTop    Caption = 'Panel1'    TabOrder = 1    object Button1: TButton      Left = 6      Top = 5      Width = 75      Height = 25      Caption = 'Button1'      TabOrder = 0      OnClick = Button1Click    end    object Button2: TButton      Left = 87      Top = 5      Width = 75      Height = 25      Caption = 'Button2'      TabOrder = 1      OnClick = Button2Click    end    object Button3: TButton      Left = 168      Top = 5      Width = 75      Height = 25      Caption = 'Button3'      TabOrder = 2      OnClick = Button3Click    end    object Button4: TButton      Left = 249      Top = 5      Width = 75      Height = 25      Caption = 'Button4'      TabOrder = 3      OnClick = Button4Click    end    object Button5: TButton      Left = 330      Top = 5      Width = 75      Height = 25      Caption = 'Button5'      TabOrder = 4      OnClick = Button5Click    end  end  object DataSource1: TDataSource    DataSet = ADODataSet1    Left = 184    Top = 112  end  object ADODataSet1: TADODataSet    CursorType = ctStatic    Parameters =     Left = 232    Top = 184  endend