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
