compute SUM for 1st row and zero total duplicates

I have sql query that uses windows function and i manage to compute the sum of records from table 2 however I’m having trouble in removing the duplicate of total from table 1 and display the sum only in row 1.

Query:

SELECT --row_number() over (partition by TABLE01.ITEMID order by TABLE01.ITEMID DESC) as rownumber,
TABLE01.ITEMID, TABLE01.WAREHOUSE, TABLE01.LOCATION, TABLE01.INVENTORY AS TABLE01INVENTORY, TABLE02.ITEMID, 
Table02.WAREHOUSE, TABLE02.LOCATION,Table02.LOCATIONNAME,table02.inventory AS TABLE02INVENTORY,
--(coalesce(table02.inventory,0)) AS TABLE02INVENTORY,
SUM(TABLE02.inventory) OVER(PARTITION BY TABLE01.ITEMID order by TABLE01.ITEMID  ) as SUMTABLE02INVENTORY--,
,(TABLE01.inventory-SUM(TABLE02.inventory) OVER(PARTITION BY TABLE01.ITEMID order by TABLE01.ITEMID )) AS VARBYSUM 
FROM Table01 LEFT JOIN Table02 ON Table01.ITEMID = Table02.ITEMID and Table01.warehouse = table02.warehouse 
GROUP BY TABLE01.ITEMID, TABLE01.WAREHOUSE, TABLE01.LOCATION, TABLE01.INVENTORY, TABLE02.ITEMID, 
Table02.WAREHOUSE, TABLE02.LOCATION, Table02.LOCATIONNAME,table02.inventory

Output:

+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEMID  | WAREHOUSE | LOCATION | TABLE01INVENTORY | ITEMID  | WAREHOUSE | LOCATION | LOCATIONNAME | TABLE02INVENTORY | SUMTABLE02INVENTORY | VARBYSUM |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 5                | ITEM001 | WHS001    | T01      | TECH001      | 1                | 6                   | -1       |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 5                | ITEM001 | WHS001    | T02      | TECH002      | 2                | 6                   | -1       |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 5                | ITEM001 | WHS001    | T03      | TECH003      | 3                | 6                   | -1       |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM002 | WHS001    | LOC002   | 4                | ITEM002 | WHS001    | T01      | TECH001      | 1                | 4                   | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM002 | WHS001    | LOC002   | 4                | ITEM002 | WHS001    | T02      | TECH002      | 3                | 4                   | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM003 | WHS001    | LOC003   | 2                | NULL    | NULL      | NULL     | NULL         | NULL             | NULL                | NULL     |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------

Expected output:

+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEMID  | WAREHOUSE | LOCATION | TABLE01INVENTORY | ITEMID  | WAREHOUSE | LOCATION | LOCATIONNAME | TABLE02INVENTORY | SUMTABLE02INVENTORY | VARBYSUM |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 5                | ITEM001 | WHS001    | T01      | TECH001      | 1                | 6                   | -1       |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 0                | ITEM001 | WHS001    | T02      | TECH002      | 2                | 0                   | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM001 | WHS001    | LOC001   | 0                | ITEM001 | WHS001    | T03      | TECH003      | 3                |                     | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM002 | WHS001    | LOC002   | 4                | ITEM002 | WHS001    | T01      | TECH001      | 1                | 4                   | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM002 | WHS001    | LOC002   | 0                | ITEM002 | WHS001    | T02      | TECH002      | 3                | 0                   | 0        |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------+
| ITEM003 | WHS001    | LOC003   | 2                | NULL    | NULL      | NULL     | NULL         | NULL             | NULL                | NULL     |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+----------

Answer

If I understand correctly, you can use ROW_NUMBER() and a CASE expression for this. The following shows this for the TABLE01INVENTORY column:

SELECT t1.ITEMID, t1.WAREHOUSE, t1.LOCATION,  
       (CASE WHEN 1 = row_number() over (partition by t1.ITEMID , t1.warehouse, t1.location order by t2.location)
             THEN t1.INVENTORY
        END) AS TABLE01INVENTORY,
       t2.ITEMID, t2.WAREHOUSE, t2.LOCATION,
       t2.LOCATIONNAME, t2.inventory AS TABLE02INVENTORY,
--(coalesce(table02.inventory,0)) AS TABLE02INVENTORY,
       SUM(t2.inventory) OVER (PARTITION BY t1.ITEMID order by t1.ITEMID  ) as SUMTABLE02INVENTORY,
       (t1.inventory - SUM(t2.inventory) OVER (PARTITION BY t1.ITEMID order by t1.ITEMID )) AS VARBYSUM 
FROM Table01 t1 LEFT JOIN
     Table02 t2 
     ON t1.ITEMID = t2.ITEMID AND
        t1.warehouse = t2.warehouse 
GROUP BY t1.ITEMID, t1.WAREHOUSE, t1.LOCATION, t1.INVENTORY, t2.ITEMID, 
         t2.WAREHOUSE, t2.LOCATION, t2.LOCATIONNAME, t2.inventory;

You can extend this for any additional columns where you want to restrict the output.

Advertisement

Answer