SQL-Sum Over Partition By Issue

I wrote a statement similar to this one. I am new to SQL and wondering how I can or if I can use SUM(___)over(partition by ___) to sum all the NonUsableTotal parts inventory for that PartID.

The issue is that f.Netflag is a numerical value either 1 or 0 to define it as usable or nonusable.

Here is the statement:

select distinct 
       l.whse,
       l.partid,
       i.descr,
       l.qty,
       case when f.Netflag=0 then 'NONUsable' else 'Usable' end as UsableStatus,
       SUM(l.qty*f.Netflag) over (partition by l.partid) as TotalUsable
from   location l (nolock)  
join   inventory i (nolock) 
on     l.partid=i.partid
join   loctable f (nolock) 
on     l.whse=f.whse

Here is what I want the results to look like:

+-------+--------+-----+------------+-----------+--------------+ 
|Partid |  Whse  | Qty |UsableStatus|TotalUsable|TotalNonUsable|
+-------+--------+-----+------------+-----------+--------------+     
 134       INSP     10    Usable         30            20
 134       RRR      15    NONUsable      30            20
 134       VVV      20    Usable         30            20
 134       QRR      5     NONUsable      30            20

I appreciate your help thank you.

You can use a CASE expression inside SUM():

SELECT  
    l.whse, l.partid, i.descr, l.qty,

    CASE WHEN f.Netflag = 0 THEN 'NONUsable' ELSE 'Usable' END 
        AS UsableStatus,
    SUM(CASE WHEN f.Netflag = 0 THEN l.qty ELSE 0 END) 
      OVER (PARTITION BY l.partid) AS TotalNonUsable,
    SUM(CASE WHEN f.Netflag = 0 THEN 0 ELSE l.qty END) 
      OVER (PARTITION BY l.partid) AS TotalUsable
FROM
    location l 
  JOIN inventory AS i ON l.partid = i.partid
  JOIN loctable AS f ON l.whse = f.whse ;

By the way, I don't see why you need DISTINCT in the query.

StackExchange Database Administrators Q#178287, answer score: 7