CREATE TABLE IF NOT EXISTS "Test1"
(
   "ID" AUTOINC,
   "PG" VARCHAR(10),
   "GSTK" INTEGER,
   "SSTK" INTEGER,
   "Fehler" VARCHAR(25),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 5
);

INSERT INTO "Test1" VALUES (1,'DI', 10, 2,'A');
INSERT INTO "Test1" VALUES (2,'DI', 0, 1,'B');
INSERT INTO "Test1" VALUES (3,'DI', 0, 2,'C');
INSERT INTO "Test1" VALUES (4,'DI', 100, 25,'B');
INSERT INTO "Test1" VALUES (5,'DI', 0, 10,'C');

* Example without aggregate expression --> OK

Select
  PG,
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG

* Example with aggregate expression --> Wrong result "SCHLECHT A", ...

Select
  PG,
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  ROUND(100*SUM(SSTK)/SUM(SSTK+GSTK) TO 2) as "SCHLECHT PROZENT", 
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG

* Example with aggregate expression --> Access Violation

Select
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  ROUND(100*SUM(SSTK)/SUM(SSTK+GSTK) TO 2) as "SCHLECHT PROZENT", 
  PG,
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG

---

 Comments and Workarounds

---

The workaround is to multi-stage the query into separate queries so that you can evaluate the expressions for the aggregates in a later query.

---

 Resolution

---

Fixed Problem on 4/23/2005 in version 4.20 build 1

---

 Products Affected

---

DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source