Datensätze zählen???

[romber]

Hallo!

Ich habe Informationen zu mehreren Hundert Rechnungen in einer Tabelle. In der Tabelle gibt es eine Spalte namens "status", alle Datensätze haben in dieser Spalte entweder "bezalt", "nicht bezahlt", oder "storniert". Die Daten werden mit einer TADOQuery dynamisch angefragt und mit DBGrid visualisiert. Wie kann ich schnell beim Laden der Daten in den DBGrid nachzählen, wie viele sind bezahlt, wie viele nicht bezahlt und wie viele storniert?

Danke!

[marabu]

Hi,

am schnellsten geht es, wenn du das Zählen dem Server überlässt:

SELECT COUNT(*) AS anzahl, status FROM tabelle GROUP BY status ORDER BY status Grüße vom marabu

[romber]

Danke für schnelle Antwort!

Und wo kommt das hin?
Und wo weise ich in dieser Code den Server darauf hin, was er zählen muss?

[omata]

Ich kann marabu nur zustimmen.

In einer Anweisung geht es zum Beispiel auch noch so...

SQL-Code:

SELECT a.*, anzahl
FROM tabelle a
LEFT JOIN (SELECT status, COUNT(*) anzahl
           FROM tabelle
           GROUP BY status) b
  ON a.status = b.status

MfG
Thorsten

[marabu]

Zitat von romber:

Und wo kommt das hin?

Direkt hinter die Query, mit der du die Daten für das Grid besorgst.

Zitat von romber:

Und wo weise ich in dieser Code den Server darauf hin, was er zählen muss?

Dazu kannst du eine WHERE Klausel benutzen, die du dir von deiner Grid-Query abgucken musst.

marabu

[romber]

Klappt irgendwie nicht.
Das mit WHERE ist mir unklar. Mein WHERE hat mit dem status nicht zu tun, ich durchsuche die Daten nach Namen des Kunden.
Unklar ist auch, wie ich dann den Wert ablese.

Ich bin ganz neu in dieser Sache. Kann mir jemand eine Codebeispiel schreiben? Danke!

[omata]

So, ich versuche es nochmal.

Bei meiner Variante ersetzt du dein jetziges SQL-Statement durch meines und dort hast du dann eine Spalte mehr, die du verarbeiten musst.

Allerdings finde ich dann doch die Variante von marabu besser.

Also irgendwo machst du doch deine jetzige Query auf.

Delphi-Quellcode:

var ADOQueryZahlen:TADOQuery;
    anzahl:integer;
begin
  ADOQueryGrid.Open;
  ADOQueryZahlen:=TADOQuery.create(Self);
  try
    ADOQueryZahlen.Connection:=ADOConnection;
    ADOQueryZahlen.SQL.Text:=
      'SELECT status, COUNT(*) anzahl'#13 +
      'FROM tabelle'#13 +
      'GROUP BY status';

    ADOQueryZahlen.Open;
    while not ADOQueryZahlen.Eof do begin

      if ADOQueryZahlen.FieldByName('status').AsString = 'B' then
        anzahl:=ADOQueryZahlen.FieldByName('anzahl').AsInteger; // bezahlt
      if ADOQueryZahlen.FieldByName('status').AsString = 'N' then
        anzahl:=ADOQueryZahlen.FieldByName('anzahl').AsInteger; // nicht bezahlt
      if ADOQueryZahlen.FieldByName('status').AsString = 'S' then
        anzahl:=ADOQueryZahlen.FieldByName('anzahl').AsInteger; // storniert

      ADOQueryZahlen.Next;
    end;
    ADOQueryZahlen.Close;
  finally
    ADOQueryZahlen.free;
  end;
TADOQuery

Hoffe, das hilft dir weiter

MfG
Thorsten

[romber]

So, tut mir Leid Leute, aber ich verstehe immer noch nicht ganz, wie ich meine WHERE-Klausel ersetze und immer noch zu dem gewünschten Ergebniss komme. Ich denke, wenn ihr mir mit einem Praktischen Beispiel zeigt, wie es geht, werden ich es besser verstehen.
Also, ich habe auf der Form ein DBGrid1, der über ADOQuery1 aus der Datenbank VERWALTUNG auf den MSSQL Server Rechnungsdaten bezieht. Die Beispieltabelle hat vier Spalten: "name", "produkt", "preis" und "status". Die ersten drei sind klar, der Spalte "status" wird einen der drei Werte zugewissen: "Bezahlt" oder "Nicht bezahlt" oder "Storniert". So, dann habe ich noch einen Edit1-Textfeld für die Eingabe des Suchbegriffs. Es werden alle vier Spalten durchgesucht:

Delphi-Quellcode:

...
var
suchbegriff: string;
begin
  with ADOQuery1 do
  begin
    active := false;
    sql.Clear;
    suchbegriff := '%' + Edti1.Text + '%';
    sql.Text := 'SELECT * FROM VERWALTUNG' + #10 + 
                'WHERE (LOWER(name) LIKE :Param1)' + #10 +
                'OR (LOWER(produkt) LIKE :Param2)' + #10 +
                'OR (LOWER(preis) LIKE :Param3)' + #10 +
                'OR (LOWER(status) LIKE :Param4)';
    ParamCheck := true;
    Parameters.ParamByName('Param1').Value := LowerCase(suchbegriff);
    Parameters.ParamByName('Param2').Value := LowerCase(suchbegriff);
    Parameters.ParamByName('Param3').Value := LowerCase(suchbegriff);
    Parameters.ParamByName('Param4').Value := LowerCase(suchbegriff);
    active := true;
    open;
  end;
...

Jetzt würde ich in der StatusBar anzeigen, wieviele Rechnungen bezahlt sind, wieviele nicht bezahlt und wiviele storniert. Diese Zahlen müssen für jeder Anfrage aktuell sein. Ich habe es zwar mit den oben geposteten Beispiele irgenddwie erreicht, dass die Datensätze gezählt wurden, aber es waren jedesmal alle Datensätze in der Tabelle gezält und nicht nur auf die Anfrage zurückgelieferte Ergebnisse.
Könnt ihr mir bitte zeigen, wie ich meine Abfrage ändern soll, damit ich zu dem gewünschten Ergebniss komme?

Im Vorraus vielen Dank!

[jensw_2000]

Hallo Romber,

ich denke, das eine kleine Stored Procedure dein Problem am besten lösen sollte.
Du kannst deine Abfrage mit den "statistischen" Ausgabeparameter sauber kombinieren.


kleines Beispiel:


Erstelle dir auf dem SQL-Server eine SP ...

SQL-Code:

CREATE PROCEDURE proc_RechnungsSuche
  @Suchbegriff       VARCHAR(50) = NULL,
  @stat_Bezahlt      INT = 0 OUTPUT,
  @stat_Offen        INT = 0 OUTPUT,
  @stat_Storniert    INT = 0 OUTPUT
AS
BEGIN

  IF @Suchbegriff IS NOT NULL
  BEGIN
    -- Suchbegriff normieren
    SET @Suchbegriff = LOWER(@Suchbegriff)

    -- Datensätze suchen und zählen
    SET NOCOUNT ON

    SELECT  @stat_Bezahlt=
            SUM( CASE WHEN LOWER(Status) = 'b' -- Counter >> bezahlt
                      THEN 1
                      ELSE 0
                 END 
               )
           ,@stat_Offen=
            SUM( CASE WHEN LOWER(Status) = 'o' -- Counter >> offen
                      THEN 1
                      ELSE 0
                 END
               )
           ,@stat_Storniert=
           SUM( CASE WHEN LOWER(Status) = 's' -- Counter >> storniert
                      THEN 1
                      ELSE 0
                 END
               ) 
    FROM    VERWALTUNG
    WHERE   (LOWER(Name) = @Suchbegriff)      -- LIKE %@Suchbegriff% geht auch
         OR (LOWER(produkt) = @Suchbegriff)   -- ist aber weniger performant
         OR (LOWER(preis) = @Suchbegriff)
         OR (LOWER(status) = @Suchbegriff)

    -- Ausgabeparameter >> NULL ersetzen
    IF @stat_Bezahlt   IS NULL  SET @stat_Bezahlt=0
    IF @stat_Offen     IS NULL  SET @stat_Offen=0
    IF @stat_Storniert IS NULL  SET @stat_Storniert=0


    -- Datensätze suchen und ausgeben
    SET NOCOUNT OFF

    SELECT  Name
           ,Produkt
           ,Preis
           ,Status
    FROM    VERWALTUNG
    WHERE   (LOWER(Name) = @Suchbegriff)      -- LIKE %@Suchbegriff% geht auch
         OR (LOWER(produkt) = @Suchbegriff)   -- ist aber weniger performant
         OR (LOWER(preis) = @Suchbegriff)
         OR (LOWER(status) = @Suchbegriff)

   -- Fertig.
   -- Die SP liefert die gewünschten Suchergebnisse und
   -- die statistischen Werte in Form von Ausgabeparametern

  END
  ELSE BEGIN
    -- Fehlermeldung falls kein Parameter übergeben wurde
    RAISERROR('Diese Procedure erfordert einen Suchbegriff !',16,1,'Parameter fehlerhaft')
  END

END

Aufruf aus Delphi:

Nimm dir eine TADoStoredProc und verbinde diese mit deiner AdoConnection und mit einer Datasource (für das Grid)

Delphi-Quellcode:

Procedure TForm1.Auftragssuche(Suchbegriff:String);
begin

{ SQL INFO
  CREATE PROCEDURE proc_RechnungsSuche
    @Suchbegriff       VARCHAR(50) = NULL,
    @stat_Bezahlt      INT = 0 OUTPUT,
    @stat_Offen        INT = 0 OUTPUT,
    @stat_Storniert    INT = 0 OUTPUT
  AS
}

  if Suchbegriff = '' then exit;

  // SP schließen
  AdoSP.Close;
  // SP Zuweisen
  AdoSP.ProcedureName:='proc_RechnungsSuche';
  // Parameter holen
  AdoSP.Parameters.Refresh;
  // Eingabeparameter setzen 
  // Ausgabeparameter müssen nicht initialisiert werden da auf dem SQL-Server Standardwert "0" gesetzt wurde
  AdoSP.Parameters[1]:=Suchbegriff;
  // SP Öffnen
  AdoSP.Open;
  // Jetzt die Ausgabeparameter holen und in der Statusbar anzeigen
  Statusbar.Panels[0].text:= 'Sum: bezahlt : ' + inttostr(AdoSP.Parameters[2]);
  Statusbar.Panels[1].text:= 'Sum: offen : ' + inttostr(AdoSP.Parameters[3]);
  Statusbar.Panels[2].text:= 'Sum: storniert : ' + inttostr(AdoSP.Parameters[4]);

  // Fertig


end;

Schöne Grüße,
Jens

[marabu]

Hi romber,

wenn du den von Jens wunderbar ausgearbeiteten Code studiert und verstanden hast, dann hast du in kurzer Zeit eine Menge gelernt oder deine hier gegebene Selbsteinschätzung war falsch. Ich möchte deine Aufmerksamkeit trotzdem noch auf ein paar andere Dinge lenken.

Zuerst solltest du überlegen, ob du nicht deine Tabellendefinition änderst. STATUS ist ein Aufzählungstyp und sollte als solcher in der Tabelle gespeichert werden. Für den Benutzer werden die Zahlenwerte dann per Lookup-Tabelle in ihre Textdarstellung überführt und umgekehrt.

Ein weiterer Punkt ist die blindwütige Suche nach Strings in allen Feldern. Was willst du mit LIKE in einem Feld Preis vom Typ MONEY suchen? Viel wichtiger als diese Suche sind für den Benutzer möglichst viele Freiheitsgrade bei der Auswahl und Anordnung der Anfrageergebnisse. Statistische Angaben sind das Ergebnis einer Auswertung und werden in der Regel sauber vom Bearbeitungsteil deines Programms getrennt.

Nur am Rande sei erwähnt, dass es für die Suche in Textfeldern mit dem LIKE Operator beim MS SQL Server eine performante Alternative gibt. Die integrierte Volltextsuche ist leicht zu konfigurieren und bei der Wortsuche mit tail truncation vielfach schneller als LIKE. Außerdem realisiert die Volltextsuche noch verschiedene andere Suchstrategien.

Freundliche Grüße vom marabu