Forum Discussion
Group by, Removing Duplicates, and Adding Rows
Hi!
I am looking for help with removing duplicates when grouping, and adding a column. Or however, I can achieve the second table (i just started off with group-by and summing but that did not work because to double counts my rows).
Here is an example:
| Key ID | Gross Sales | Exempt Sales | NonTaxed Sales | Amount Subject to Tax | Sales Tax | Rate |
00001 | 100.00 | 0 | 0 | 100.00 | 6.00 | 6% |
| 00001 | 100.00 | 0 | 0 | 100.00 | 1.00 | 1% |
| 00001 | 100.00 | 0 | 0 | 100.00 | 0.50 | 0.5% |
| 00001 | 100.00 | 0 | 0 | 100.00 | 2.00 | 2% |
| 00002 | 50.00 | 0 | 50.00 | 0 | 0 | 0 |
| 00002 | 20.00 | 0 | 0 | 20.00 | 10.00 | 50% |
| 00003 | 75.00 | 75.00 | 0 | 0 | 0 | 0 |
| 00004 | 30.00 | 0 | 30.00 | 0 | 0 | 0 |
| 00004 | 10.00 | 0 | 0 | 10.00 | 1.00 | 10% |
| 00004 | 30.00 | 0 | 30.00 | 0 | 0 | 0 |
| 00004 | 10.00 | 0 | 0 | 10.00 | 2.00 | 20% |
Above we have instances where I there are duplicates in several columns. However, the driving factors are the Gross Sales and the Rate, along with the transaction ID. However, since there are different sales taxes depending on jurisdiction, the rows repeat for the transaction (even though they are blank since the exempt and non-taxable amount drive the rate (if there is something in this column then the rate is 0).
This is what I would like the column to look like.
| Key ID | Gross Sales | Exempt Sales | NonTaxed Sales | Amount Subject to Tax | Sales Tax | Rate |
00001 | 100.00 | 0 | 0 | 100.00 | 9.50 | 7.5% |
| 00002 | 70.00 | 0 | 50.00 | 20.00 | 10.00 | 50% |
| 00003 | 75.00 | 75.00 | 0 | 0 | 0 | 0 |
| 00004 | 40.00 | 0 | 30.00 | 10.00 | 3.00 | 30% |
I would like one row per key. If the values in each colum (per key) I would like them to be added, other wise they will be double counted and that is incorrect. So if the values are different, I would like them to be added.
Thank you!
