CODEWITHSUNDEEP
mysqlsql
dhie
dhie

Reputation: 89

MySQL:How to add specified rows

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

Upvotes: 1

Views: 51

Answers (2)

Anuruddha
Anuruddha

Reputation: 3245

Try this. Replace t1 with your table name

SELECT GROUP_CONCAT(PK SEPARATOR '+'), Class ,SUM(Amount) AS 
TotalAmount FROM 
(SELECT DISTINCT PK, Comp, Amount, Class FROM t1) AS t2  
GROUP BY Class;

Upvotes: 1

G.Arima
G.Arima

Reputation: 1171

Try this out:

select Class ,SUM(Amount) AS TotalAmount from 
(select distinct pk,comp,amount,class from have) 
group by class;

Upvotes: 1

Related Questions