Aggregate Aggravation - Mailing list pgsql-sql

I have a query using the SUM() function that is not returning the 
appropriate results.

There are 3 queries with results below.  Query 1 is the type of query I'd
like to use, that has the SUM() function and a join with a customer table
(bolcustomer) to constrain the results to a particular customer.  The
"sum" field ends up being 78 for this particular car.  Query 2 is the same
query without the customer table (bolcustomer) qualifier.  This qualifier
may seem superfluous in these test queries, but in real life the customer
constraint is necessary because the other ones (car number and arrival
date) are not present.  I had to add them to get a snapshot suitable for
testing and for this post.  As you can see, Query 2 returns the proper sum
of 64.  Query 3 and accompanying results illustrate why 64 is correct.
Furthermore, there were about 100 cars involved in the "real life"
application of this query, and only 2 cars produced the "inappropriate"
results (including the car in this example - BCOL730563) ... so 98% of the
time it appeared to work.  That's why I was wondering if the SUM()
function had something to do with the problem.

My question is somewhat open-ended: does anyone see anything fundamentally
wrong with Query 1?  I'd really like to use it or it's functional
equivalent if possible.  What is it about adding "inventorydebit" to the
"FROM" clause, "inventorydebit.bol_number = bolcustomer.bol_number" to the
"WHERE" clause, and "bolcustomer.customer_id" to the "GROUP BY" clause
that causes the query to return the sum of 78 instead of 64?

Thank you for reading my post.

Best,

-Robin



-- Query 1
----------
SELECT       inventorydebit.car_number,       inventorydebit.arrival_date,       SUM(inventorydebit.debit_amount)
FROM       inventorydebit,       bolcustomer
WHERE       inventorydebit.bol_number = bolcustomer.bol_number
AND       inventorydebit.arrival_date = '25-Jun-2001'
AND       inventorydebit.car_number = 'BCOL730563'
AND       bolcustomer.customer_id = '105'
GROUP BY       inventorydebit.arrival_date,       inventorydebit.car_number;

car_number  |arrival_date|sum
------------+------------+---
BCOL730563  |  06-25-2001| 78
(1 row)


-- Query 2
----------
SELECT  inventorydebit.car_number,  inventorydebit.arrival_date,  SUM(inventorydebit.debit_amount)
FROM       inventorydebit
WHERE  inventorydebit.car_number = 'BCOL730563'
AND       inventorydebit.arrival_date = '25-Jun-2001'
GROUP BY  inventorydebit.arrival_date,  inventorydebit.car_number;

car_number  |arrival_date|sum
------------+------------+---
BCOL730563  |  06-25-2001| 64
(1 row)


-- Query 3
----------
SELECT       bol_number,       debit_amount
FROM       inventorydebit
WHERE       car_number = 'BCOL730563'
AND       arrival_date = '25-Jun-2001';

bol_number|debit_amount
----------+------------    88190|           2    88503|          16    88514|          16    88595|          16
88738|         14
 
(5 rows)


TABLES INVOLVED....

pcireload=> \d inventory
Table    = inventory
+----------------------------------+-----------------------------+-------+
|              Field               |              Type           | Length|
+----------------------------------+-----------------------------+-------+
| car_number                       | char() not null             |    12 |
| arrival_date                     | date not null               |     4 |
| line_item_seq                    | int2 not null               |     2 |
| line_item_qty                    | int2                        |     2 |
| line_item_desc                   | char()                      |    50 |
| pieces_per_unit                  | int2                        |     2 |
| material_location                | char()                      |    30 |
| inventory_user                   | char()                      |    12 |
+----------------------------------+-----------------------------+-------+
Index:    inventory_pk

pcireload=> \d inventorydebit
Table    = inventorydebit
+----------------------------------+-----------------------------+-------+
|              Field               |              Type           | Length|
+----------------------------------+-----------------------------+-------+
| bol_number                       | int4 not null               |     4 |
| car_number                       | char() not null             |    12 |
| arrival_date                     | date not null               |     4 |
| line_item_seq                    | int2 not null               |     2 |
| debit_amount                     | int2                        |     2 |
+----------------------------------+-----------------------------+-------+
Index:    inventorydebit_pk

pcireload=> \d bolcustomer
Table    = bolcustomer
+----------------------------------+-----------------------------+-------+
|              Field               |              Type           | Length|
+----------------------------------+-----------------------------+-------+
| bol_number                       | int4 not null               |     4 |
| customer_id                      | char() not null             |     4 |
| bol_customer_type                | char() not null             |    20 |
| po_number                        | char()                      |    15 |
+----------------------------------+-----------------------------+-------+
Index:    bolcustomer_pk