CODEWITHSUNDEEP
sqlsumduplicates
pgunston
pgunston

Reputation: 302

How can I remove duplicates in SQL and SUM a field for each instance of duplication?

I have a table in SQL which lists details for invoices.

Every invoice can have multiple items. For instance, in the table below, you can see multiple "ferry service" lines. This is because there is ONE invoice however, there are five items on that invoice (all with different amounts) therefore, "ferry service" is listed five times. On the other hand, the top line, "Tank Liner" has only one item on the invoice therefore, it appears only once.

I want to be able to list each Description only once (remove duplicates) however, I want the "Amount" field to display the SUM of all amounts. How can I achieve this?

+--------------------------------+--------------------------------+----------+----+--------------+--------+-----------+
| Description                    | LineDescription                | Amount   | UM | UnitCost     | Units  | GrossAmt  |
+--------------------------------+--------------------------------+----------+----+--------------+--------+-----------+
| Tank Liner                     | 3 FBE Bolted Steel Circular L  | 18035.13 | EA | 327911.30000 | 1.000  | 327911.30 |
| Delivery Tanks to Site         | Bolted Steel Liner Tank        | 8184.92  | EA | 148816.76000 | 1.000  | 148816.76 |
| Circular Tanks                 | Circular Tank (Inv 4556)       | 5689.78  | EA | 103450.66000 | 1.000  | 103450.66 |
| Fit Ext Nut Caps to Tank 2     | Fit External Nut Caps to Tank  | 2141.93  | EA | 1947.21000   | 1.000  | 1947.21   |
| Replace Platform on Tank 3     | Replace Platform on Tank 3 --  | 43356.50 | EA | 39415.00000  | 1.000  | 39415.00  |
| Retention held Inv 1758        | Road Furniture and Street Sign | 7621.22  | LS | 0.00000      | 0.000  | 11138.00  |
| ferry service                  | Fares to QIR                   | -50.00   | EA | 45.45400     | -1.000 | -45.45    |
| ferry service                  | Fares to QIR                   | -699.00  | EA | 635.45400    | -1.000 | -635.45   |
| ferry service                  | Fares to QIR                   | 69.99    | EA | 63.64000     | 1.000  | 63.64     |
| ferry service                  | Fares to QIR                   | -60.01   | EA | 54.54500     | -1.000 | -54.55    |
| ferry service                  | Fares to QIR                   | 275.00   | EA | 250.00000    | 1.000  | 250.00    |
| Rentry of invoice 104759A      | Tank 2 - 2.5ML                 | 95039.64 | EA | 103450.60000 | 1.000  | 103450.60 |
| Rentry of invoice 104759A      | Tank 2 - 2.5ML                 | 20631.62 | EA | 103450.60000 | 1.000  | 103450.60 |
| Rent8-10Rooksby;21&15Morgan    | 8-10 Rooksby W/shop office May | 1560.63  | LS | 0.00000      | 0.000  | 1418.75   |
| Rent8-10Rooksby;21&15Morgan    | 15 Morgan St Fabrication May14 | 2079.18  | LS | 0.00000      | 0.000  | 1890.16   |
| Credit J.Wagner wrong sizeBoot | JOE WAGNER-CREDIT WRONG SIZE B | -127.60  | EA | 116.00000    | -1.000 | -116.00   |
| Pipe Raiser and Freight        | Supply 65 x 600mm Gal Pipe Ris | 114.40   | EA | 52.00000     | 2.000  | 104.00    |
| Pipe Raiser and Freight        | Freight                        | 22.00    | EA | 20.00000     | 1.000  | 20.00     |
| Phil Mansfield - Uniforms      | PHIL MANSFIELD-JEANS SZ 77R    | 92.40    | EA | 28.00000     | 3.000  | 84.00     |
| traffic management             | Traffic Controllers x 3, vehi  | 1162.43  | EA | 1056.75000   | 1.000  | 1056.75   |
| Belts                          | 11A1030 Gates Belts            | 24.64    | EA | 11.20000     | 2.000  | 22.40     |
| Relay, Tubing, Buzzer, Cable T | CPX4150 Service Valve Core Hig | 36.52    | EA | 33.20000     | 1.000  | 33.20     |
| Relay, Tubing, Buzzer, Cable T | ACX5018BL Warning Buzzer 24V 7 | 11.28    | EA | 10.25000     | 1.000  | 10.25     |
| Relay, Tubing, Buzzer, Cable T | ACX1294 Heat Shrink D/Wall Bla | 12.10    | EA | 11.00000     | 1.000  | 11.00     |
+--------------------------------+--------------------------------+----------+----+--------------+--------+-----------+

For example, instead of this:

+---------------+-----------------+---------+
| Description   | LineDescription | Amount  |
+---------------+-----------------+---------+
| ferry service | Fares to QIR    | -50.00  |
| ferry service | Fares to QIR    | -699.00 |
| ferry service | Fares to QIR    | 69.99   |
| ferry service | Fares to QIR    | -60.01  |
| ferry service | Fares to QIR    | 275.00  |
+---------------+-----------------+---------+

I want to see this:

+---------------+-----------------+---------+
| Description   | LineDescription | Amount  |
+---------------+-----------------+---------+
| ferry service | Fares to QIR    | -464.02 |
+---------------+-----------------+---------+

FYI, this is my query:

select Description, LineDescription, Amount, UM, UnitCost, Units, GrossAmt, from brvAPAllInvoices 
Where PaidDate is null and APCo = 1 and Type = 'P'

Upvotes: 0

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

What you want is a basic aggregation using group by, but the challenge is LineDescription. For your example, they are all the same. For others, they are different. Here is one thing you cando:

select description, min(LineDescription) as LineDescription, sum(Amount) as Amount
from table t
group by description;

EDIT:

You need to use aggregation functions for all columns not in the group by. Here is my best guess on what might help you:

select Description, min(LineDescription), sum(Amount), min(UM), avg(UnitCost),
       sum(Units), sum(GrossAmt)
from brvAPAllInvoices 
Where PaidDate is null and APCo = 1 and Type = 'P'
group by Description;

Upvotes: 1

lc.
lc.

Reputation: 116528

This is what GROUP BY is for. You need to GROUP BY the Description and LineDescription columns:

SELECT Description, LineDescription, SUM(Amount) AS Amount
FROM Table1
GROUP BY Description, LineDescription

Note that this will produce a different output row for each combination of Description and LineDescription.

Upvotes: 2

Related Questions