Skip to content
Advertisement

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:

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     |
+---------+-----------+----------+------------------+---------+-----------+----------+--------------+------------------+---------------------+---------- 

Advertisement

Answer

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

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement