Board index » delphi » Problem with SQL-SUM function

Problem with SQL-SUM function

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

 

Re:Problem with SQL-SUM function


Try changing it like this
Group by must include the fields y selected.

Quote
> 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", custom."CustomName"

Per H.

Re:Problem with SQL-SUM function


<snip>

Try This... it should work

SELECT DISTINCT D.CustNo, D.CustName, SUM(D1.CashPerOrder)
FROM "CUSTOMER.DB" D, "ORDER.DB" D1
WHERE
(D1.CustNo = D.CustNo)
group BY D.CustNo, D.CustName
ORDER BY D.CustNo, D.CustName

regards /Bernard

Re:Problem with SQL-SUM function


Quote
>SELECT DISTINCT D.CustNo, D.CustName, SUM(D1.CashPerOrder)
>FROM "CUSTOMER.DB" D, "ORDER.DB" D1
>WHERE
>(D1.CustNo = D.CustNo)
>group BY D.CustNo, D.CustName
>ORDER BY D.CustNo, D.CustName

        A) You should not need the order by with group by, group should do an order by

        B) I don't think the BDE will let you specify an order with a group, I think it
will generate an error.

_
NOTE: This software is currently in early alpha. If you notice any
problems, or RFC non-compliance, please report it to p...@pbe.com
 \------------------------------------------------------------\
  \           Chad Z. Hower  -  phoe...@pobox.com              \
   \  Phoenix Business Enterprises - p...@pbe.com - www.pbe.com  \
    \     Physically in Church Hill, TN - Logically Not Sure     \
     \------------------------------------------------------------\

Quote
>>SQUID - The ultimate 95/NT offline databasing reader

**Special Compile: 3.000A (Alpha)

Other Threads