CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

How to exclude rows from sum but still show them?

I have a table itemsInShippment with the following data:

itemid shippmentid qty
10         1       100
20         1       200
10         2       300
10         3       1000

and table shippments

shippmentid date         shippmentstatus    supplierid
1           2015-01-12         OK               5000
2           2015-01-17         OK               5000
3           2015-01-17         Cancelled        5000

I need to write a query that shows this details about specific shippment say shipmentid 1. My given parameters are supplierid and date. together they related to one shipment (unique).

For supplierid=5000 and date=2015-01-12 I want to get:

itemid        qty               qtyInOtherShipments
10            100                    300                      //1000 is canceled.
20            200                      0

My query works fine without considering the cancelled: 

SELECT cte.* 
FROM 
    (SELECT
         a.itemid, b.date, a.qty,
         (coalesce( SUM(a.qty) OVER (PARTITION BY a.itemid), 0) - 
          coalesce( SUM(a.qty) OVER (PARTITION BY a.itemid, a.shipmentid)  ,0)  ) AS qtyInOtherShipments,
     FROM
         itemsInShippment a
     LEFT JOIN
         shippments b using (shippmentid)
     WHERE 
         b.supplierid = 5000) AS cte
  WHERE 
      cte.date = '2015-01-12'

the cte must be this way as in qtyInOtherShipments I Sum the total qty and then remove my own qty. In order to sum the total qty I can't do WHERE d.date=... inside I must do that outside.

This query gives:

itemid        qty               qtyInOtherShipments
10            100                    1300
20            200                    0

I'm having trouble taking under consideration the cancelled shipments. if I change the Where to : 

where b.supplierid = 5000 and b.shippmentstatus not like 'cancelled'

it works... I will see:

itemid        qty               qtyInOtherShipments
10            100                    300              
20            200                      0

but if I run the query on cancelled shipments (supplierid=5000 and date=2015-01-17) I will get:

itemid        qty               qtyInOtherShipments
nothing

what I should have get is:

itemid        qty               qtyInOtherShipments
10            1000                      300

so my problem is that I don't want to sum itemid that is related to cancelled but I still want to see this rows.

How do I get the correct result?

Upvotes: 2

Views: 1922

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324405

A more efficient variant of Florian's answer exists for PostgreSQL 9.4, the filter clause for an aggregate.

SUM (a.qty) FILTER (WHERE b.shippmentstatus <> 'cancelled') OVER (PARTITION BY ...

See FILTER in the docs for aggregates. It's basically a mini-WHERE clause that applies only for that aggregate.

Thanks to @a_horse_with_no_name for pointing it out earlier.

Upvotes: 3

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Try Below query

create table #itemsInShippment (itemid int, shippmentid int, qty int)

insert into #itemsInShippment (itemid, shippmentid, qty)
SELECT 10 as itemid,   1 as shippmentid,       100 as qty  UNION
SELECT 20  ,   1,       200   UNION
SELECT 10  ,   2,      300   UNION
SELECT 10  ,   3,      1000


CREATE TABLE #shippments (shippmentid int , dt date,  shippmentstatus varchar(50),    supplierid int)

insert into #shippments (shippmentid, dt, shippmentstatus,supplierid)
SELECT 1 as shippmentid,     '2015-01-12' as dt,         'OK' as shippmentstatus    ,           5000 as supplierid UNION ALL
SELECT 2,           '2015-01-17',         'OK'      ,         5000 UNION ALL
SELECt 3,           '2015-01-17' ,        'Cancelled'   ,     5000 




SELECT cte.* 
    FROM (

          select a.itemid,b.dt,a.qty,
                 (coalesce( SUM(case when shippmentstatus <> 'Cancelled' then a.qty else 0 end) OVER (PARTITION BY a.itemid)  ,0) - 
                   coalesce( SUM(case when shippmentstatus <> 'Cancelled' then a.qty else 0 end) OVER (PARTITION BY a.itemid,a.shippmentid)  ,0)  )
                   AS qtyInOtherShipments
          from #itemsInShippment a
          left join #shippments b on a.shippmentid = b.shippmentid                                 
          where b.supplierid = 5000  --and shippmentstatus = 'Cancelled'
        ) as cte
  where cte.dt='2015-01-12'

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

You want to exclude canceled items only from sums. So, do not filter them with where, just filter them on sums:

SUM(case when b.shippmentstatus <> 'cancelled' then a.qty end) OVER (PARTITION BY ...

Sum does not take in consideration null, that's why the above works. (When status is canceled the case expression will return null.)

Upvotes: 3

Related Questions