From: Robin's PG-SQL List <pg-sql@mail.robin.net>
To: pgsql-sql@postgresql.org
Subject: Aggregate Aggravation
Date: Wed, 26 Sep 2001 15:43:51 -0700 (MST)
Message-ID: <Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pg-sql@mail.robin.net
Subject: Re: Aggregate Aggravation
In-Reply-To: <Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net>
References: <Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox