Example table
+----+---------+--------+-------+ | PK | Comp | Amount | Class | +----+---------+--------+-------+ | A1 | Alpha | 2.00 | BA | | A1 | Alpha | 2.00 | BA | | B1 | Bravo | 1.50 | BA | | B1 | Bravo | 1.50 | BA | | C1 | Charlie | 2.00 | BC | | C1 | Charlie | 2.00 | BC | | D1 | Delta | 2.20 | BC | | D1 | Delta | 2.20 | BC | | D2 | Delta | 1.70 | BC | | D2 | Delta | 1.70 | BC | | E1 | Echo | 3.10 | BD | | E1 | Echo | 3.10 | BD | | E2 | Echo | 3.00 | BD | | E2 | Echo | 3.00 | BD | | E3 | Echo | 2.90 | BD | | E3 | Echo | 2.90 | BD | | E4 | Echo | 2.50 | BD | | E4 | Echo | 2.50 | BD | | E5 | Echo | 3.10 | BD | | E5 | Echo | 3.10 | BD | +----+---------+--------+-------+ How could I ADD the first value of each PK? I've been using
SUM(Amount) AS TotalAmount...GROUP BY Class
this is what the result looks like.
+----+---------+--------+-------+-------------+ | PK | Comp | Amount | Class | TotalAmount | +----+---------+--------+-------+-------------+ | A1 | Alpha | 2.00 | BA | 7.00 | | C1 | Charlie | 2.00 | BC | 11.80 | | E1 | Echo | 3.10 | BD | 29.20 | +----+---------+--------+-------+-------------+ It added all the Amount.
I've used SUM(MAX(Amount)) AS TopAmount but got error.
I would like to have this table
+----+---------+--------+-------+-------------+ | PK | Comp | Amount | Class | TotalAmount | +----+---------+--------+-------+-------------+ | A1 | Alpha | 2.00 | BA | 3.50 | | C1 | Charlie | 2.00 | BC | 5.90 | | E1 | Echo | 3.10 | BD | 14.6 | +----+---------+--------+-------+-------------+ Like this but just the first data of each PK
+-------------------+-------+-------------+ | Amount | Class | TotalAmount | +-------------------+-------+-------------+ | (A1+B1) | BA | 3.50 | | (C1+D1+D2) | BC | 5.90 | | (E1+E2+E3+E4+E5) | BD | 14.6 | +-------------------+-------+-------------+ Thank you for your time..
