pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (3+ messages)  latest in thread

Message-ID: <Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net>
Permalink:  ../Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net/
Also on:    postgresql.org/message-id/Pine.LNX.3.96.1010926152227.6660D-100000@mail.robin.net

 · 

reply

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