pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Aggregate Aggravation
3+ messages / 2 participants
[nested] [flat]

* Aggregate Aggravation
@ 2001-09-26 22:43  Robin's PG-SQL List <pg-sql@mail.robin.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Robin's PG-SQL List @ 2001-09-26 22:43 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Aggregate Aggravation
@ 2001-09-27 17:31  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Robin's PG-SQL List <pg-sql@mail.robin.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2001-09-27 17:31 UTC (permalink / raw)
  To: Robin's PG-SQL List <pg-sql@mail.robin.net>; +Cc: pgsql-sql

"Robin's PG-SQL List" <pg-sql@mail.robin.net> writes:
> I have a query using the SUM() function that is not returning the 
> appropriate results.

I'm guessing that you have two rows in bolcustomer matching
bol_number = 88738, so that the 14 gets added in twice.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Aggregate Aggravation
@ 2001-09-28 06:33  Robin's PG-SQL List <pg-sql@mail.robin.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Robin's PG-SQL List @ 2001-09-28 06:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql


Indeed I do.  I need to add another constraint on that query.  It
never would have occurred to me had I not asked.  Problem solved.  Thank
you, thank you!

-Robin

On Thu, 27 Sep 2001, Tom Lane wrote:

> "Robin's PG-SQL List" <pg-sql@mail.robin.net> writes:
> > I have a query using the SUM() function that is not returning the 
> > appropriate results.
> 
> I'm guessing that you have two rows in bolcustomer matching
> bol_number = 88738, so that the 14 gets added in twice.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-09-28 06:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-26 22:43 Aggregate Aggravation Robin's PG-SQL List <pg-sql@mail.robin.net>
2001-09-27 17:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-09-28 06:33   ` Robin's PG-SQL List <pg-sql@mail.robin.net>

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