Hi all
To come to the most important point :), I'll describe
my problem right away (BTW, I'm using Delphi 1.0):
there are two tables:
order.db (OrderNumber,CustomNumber,CashPerOrder)
custom.db (CustomNumber,CustomName)
CustomNumber is the main index in custom.db, whereas there
no index in order.db!
The contents of the tables could look like this:
order.db:
OrderNumber CustomNumber CashPerOrder
1000 1 100.0
1001 1 101.0
1002 1 110.0
1003 1 101.0
1004 1 10.0
2001 2 10200.0
2002 2 2100.0
2003 2 1400.0
3000 3 10.0
which means: there is only ONE OrderNumber, but several
orders for one customer
custom.db:
CustomNumber CustomName
1 Fred
2 Bill
3 Anne
which means: there is only ONE CustomNumber (it's an
autoincrement AND the main index of custom.db) and one
name per customer.
Now what I want to do is the following: calculate the
sum of ALL CashPerOrder entries PER customer, print this
sum together with CustomName and CustomNumber. So far I've
managed to write this sql-statement, using a TQuery-Object:
SELECT order."CustomNumber",SUM(order."CashPerOrder") AS total
FROM "order.db"
WHERE (order."CustomNumber" LIKE order."CustomNumber")
GROUP BY order."CustomNumber"
This works fine, but, the name is missing. So I wrote:
SELECT order."CustomNumber",custom."CustomName",
SUM(order."CashPerOrder") AS total
FROM "order.db","custom.db"
WHERE ( (order."CustomNumber" LIKE order."CustomNumber") AND
(order."CustomNumber" LIKE customr."CustomNumber") )
GROUP BY order."OrderNumber"
This fails miserably. Furthermore, I've read a book about SQL which
stated, that it is NOT possible to use a combination of aggregate
(like SUM(),MIN(),...) and non-aggregate functions in the same
SELECT-statement. Look at the first SQL-statement - it works fine.
What shall I do?
Sincerely
Joerg
--
Joerg Guenther Email: guent...@ikf.uni-frankfurt.de
Institut fuer Kernphysik office phone: +49-(0)69-798-24254
Universitaet Frankfurt/Main office fax: +49-(0)69-798-24212
August-Euler-Str. 06 60486 Frankfurt/Main - FRG
