Aggregate query problem

[sbowden81]

Hi all,

I'm trying to create what I thought was a simple inventory query, but I'm having a problem. I have a shipment table that looks like this:

SHIP_ID SHIP_QTY
1 24
2 12

Then a table that shows when certain items from a shipment are "processed":

SHIP_ID PROCESSED_QTY
1 2
1 3

As you can see, a shipment can occur in the processed table multiple times, which leads to problems when I try to do the following query:

 
SELECT s.CLI_CODE as "Client", Sum(s.SHIP_QTY) - Sum(p.PROC_QTY) AS "No. of pallets on hand"
FROM SHIPMENT s, PROCESSED p
WHERE s.SHIP_ID = p.SHIP_ID
GROUP BY s.CLI_CODE;
 
 

What happens is that the shipment quantity is added twice due to the duplicate entries in the processed table, resulting in 48-5, instead of 24-5 like I want. Anyone have any ideas? I need to keep the sum on the SHIP_QTY column due to multiple shipments from the same client. Thanks!

[gintsp]

Something like

SELECT cli_id, ship_id, s1 - s2 FROM
(
  SELECT cli_id, ship_id, sum(ship_qty) s1
  FROM shipment
  GROUP BY ship_id, cli_id
) a,
(
  SELECT ship_id, sum(proc_qty) s2
  FROM processed
  GROUP BY ship_id
) b
WHERE a.ship_id = b.ship_id

BTW it is not clear from your description how many shipments one client may have.

Gints Plivna
http://www.gplivna.eu

[sbowden81]

Something like

SELECT cli_id, ship_id, s1 - s2 FROM
(
  SELECT cli_id, ship_id, sum(ship_qty) s1
  FROM shipment
  GROUP BY ship_id, cli_id
) a,
(
  SELECT ship_id, sum(proc_qty) s2
  FROM processed
  GROUP BY ship_id
) b
WHERE a.ship_id = b.ship_id

BTW it is not clear from your description how many shipments one client may have.

Gints Plivna
http://www.gplivna.eu

Thanks, I originally did something like that, but as per your question there will be many shipments from certain clients. The result from that query will return onhand totals for each shipment, whereas I would like a single onhand total for each client.

[amitpatel66]

sbowden81,

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[amitpatel66]

Thanks, I originally did something like that, but as per your question there will be many shipments from certain clients. The result from that query will return onhand totals for each shipment, whereas I would like a single onhand total for each client.

From your comments, my understanding is:

Eg:

Sample Data:

client shipment qty
1 1 24
1 2 24

shipid pro_qty
1 2
1 2
2 1
2 1

output you require is:

clientid shipmentid qtyonhand
1 1 20
1 2 22

This is what you require?