Excluding duplicate items from a query using 'group by'

Question

I have three tables:

table 'received'
------------------
partner_id int
item_id int

table 'delivered'
------------------
item_id int
delivery_date date
customer_id int

table 'partners'
------------------
id int
name text

table 'customers'
------------------
id int
name text

What I'd like to query is which items have been delivered by which partners in a single delivery to a customer. Sometimes different partners deliver the same items, which should be filtered out as the delivered items don't contain dupes.

What I've come up with is this:

select 
  partner_id, 
  count(distinct item_id) 
from 
  received 
where item_id in 
  (select distinct item_id from delivered where delivery_date = '2010-07-14' and customer_id = 1)
group by partner_id;

Yet this gives me all delivered items including the dupes the partners have delivered. I have been thinking about this for a long time now, and have tried sub-selects using 'except', 'having' and others, but haven't gotten to a point that took me further.

I'd be greatful for any hints into the right direction. Thank you.

-- Edit --

Here's some sample data:

table 'received'

partner_id | item_id 
-----------|---------
1          | 1
1          | 2
2          | 1
2          | 3

table 'delivered'

item_id | delivery_date | customer_id
--------|---------------|------------
1       | 2010-07-14    | 1
2       | 2010-07-14    | 1
3       | 2010-07-14    | 1

The current output is:

partner | amount
--------|------
1       | 2
2       | 2

The desired output is:

partner | amount
--------|------
1       | 2
2       | 1

Since the partner with ID 2 has delivered an item that was already delivered by partner 1.

Answer 1

select 
  partner_id, count(distinct item_id) 
from 
  received join delivered using (item_id)
where 
  delivery_date = '2010-07-14' and customer_id = 1
group by partner_id;

After update, your problem does not seem to be well defined. Why is item 1 counted for one customer and not the other? Why is it only counted for one of them, if both had delivered it?

You could try it like:

select 
  partner_id, count(distinct item_id) 
from (
  select distinct on (item_id) partner_id, item_id
  from received join delivered using (item_id)
  where delivery_date = '2010-07-14' and customer_id = 1
  order by item_id, partner_id
)
group by partner_id;

Answer 2

You have to have a reason for assigning each delivery to one partner. In your example, you seem to have decided to assign it to the lower number. You would do that something like this:

(select min(partner_id) partner_id, item_id from received group on item_id) rec_assign

Then add the partner_id of the delivering partner to the delivered table:

(select delivered.*, rec_assign.partner_id from delivered inner join (select min(partner_id) partner_id, item_id from received group on item_id) rec_assign on delivered.item_id = rec_assign.item_id) ) as del_mod

Now it's simple

select partner_id, count() from (select delivered., rec_assign.partner_id from delivered inner join (select min(partner_id) partner_id, item_id from received group on item_id) rec_assign on delivered.item_id = rec_assign.item_id) ) as del_mod group by partner_id order by partner_id

I think that should do it, modulo silly errors.