Counting in a database table

[danten]

Hello.
I need advice on how to count multiple entries in a database table from the third column to the last column in the whole table.
I need to count how many times the table number 1 and 10.
I use this code and it is not good:

Delphi-Quellcode:

procedure Tfrm_main._NE;
var
  i,y: integer;
begin
  Application.ProcessMessages;
with Table do
  begin
    DisableControls;
    y := 0;
    try
      First;
    while not EOF do
    begin
    for I := Table.FieldDefs.Count -1 downto 2 do
    begin
    if Pos('1', Table.FieldByName(Table.FieldDefs[i].Name).AsString) > 0 then
    begin
      y := y + 1;
      Label1.Caption := IntToStr(y) + ' x';
      Application.ProcessMessages;
    end;
    if Pos('10', Table.FieldByName(Table.FieldDefs[i].Name).AsString) > 0 then
    begin
      y := y + 1;
      Label2.Caption := IntToStr(y) + ' x';
      Application.ProcessMessages;
    end;
    end;
     Next;
     Application.ProcessMessages;
    end;
    finally
      EnableControls;
      Application.ProcessMessages;
    end;
  end;

The result is always the same!

[jobo]

Why do You use same counting variable vor 1 and 10?
Is it intended to double count 1 if there is a 10?

Why don't You use a query, to do the count?

[danten]

Column names are not known in advance.
Now counts correctly.
y := 0;
y1 := 0;
for I := Table.FieldDefs.Count -1 downto 2 do
begin
if Pos('1', Table.FieldByName(Table.FieldDefs[i].Name).AsString) > 0 then
begin
y := y + 1;
Label1.Caption := IntToStr(y) + ' x';
Application.ProcessMessages;
end;
if Pos('10', Table.FieldByName(Table.FieldDefs[i].Name).AsString) > 0 then
begin
y1 := y1 + 1;
Label2.Caption := IntToStr(y1) + ' x';
Application.ProcessMessages;
end;
end;

[p80286]

please give us an example of your data in the Table..

vergisses..

[danten]

OK, here's a picture.

[p80286]

first it might be better to use Integer-Fields thean String Fields, to avoid double counting.

second all Fields inthe Table should be defined and knowed, so you better use a Query like

Code:

select field1,field2,field3...
frommy mytable
where field1=1
  or field2=1
  or ....
  or fieldx=10

that shoudn't be the fastes way but better than reading thousends of records for a dozend records which really count.

Delphi-Quellcode:

procedure Tfrm_main._NE;
var
  i: integer;
  cnt1 : integer;
  cnt10: integer;
begin
  Application.ProcessMessages;
with Table do
  begin
    DisableControls;
    cnt1:=0;
    cnt10:=0;
    try
      First;
      while not EOF do
      begin
        for I := Table.FieldDefs.Count -1 downto 2 do
        begin
          case Table.FieldByName(Table.FieldDefs[i].Name).AsInteger of
            1 : begin
                  cnt1:= cnt1 + 1;
                  Label1.Caption := IntToStr(cnt1) + ' x';
                  Application.ProcessMessages;
                end;
           10 : begin
                  cnt10 := cnt10 + 1;
                  Label2.Caption := IntToStr(cnt10) + ' x';
                  Application.ProcessMessages;
                end;
        end;
        Next;
        Application.ProcessMessages;
      end;
    finally
      EnableControls;
      Application.ProcessMessages;
    end;
  end;

K-H

[danten]

OK.
How then display the result in Label.Caption?

[danten]

Zitat von p80286:

first it might be better to use Integer-Fields thean String Fields, to avoid double counting.

second all Fields inthe Table should be defined and knowed, so you better use a Query like

Delphi-Quellcode:

        for I := Table.FieldDefs.Count -1 downto 2 do
        begin
          case Table.FieldByName(Table.FieldDefs[i].Name).AsInteger of
            1 : begin
                  cnt1:= cnt1 + 1;
                  Label1.Caption := IntToStr(cnt1) + ' x';
                  Application.ProcessMessages;
                end;
           10 : begin
                  cnt10 := cnt10 + 1;
                  Label2.Caption := IntToStr(cnt10) + ' x';
                  Application.ProcessMessages;
                end;

K-H

Thank you, now it counts quickly and correctly.
I somehow forgot the Case: