MySQL:How to add specified rows

Question

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

I can not understand which out put do you want ?

krishn Patel
– krishn Patel

2018-02-08 06:59:04 +00:00
Commented Feb 8, 2018 at 6:59 — krishn Patel
– krishn Patel, Commented Feb 8, 2018 at 6:59
I've edited my post sir/madam. Hope I elaborated much.

dhie
– dhie

2018-02-08 07:07:44 +00:00
Commented Feb 8, 2018 at 7:07 — dhie
– dhie, Commented Feb 8, 2018 at 7:07
can you show your query ?

krishn Patel
– krishn Patel

2018-02-08 07:13:29 +00:00
Commented Feb 8, 2018 at 7:13 — krishn Patel
– krishn Patel, Commented Feb 8, 2018 at 7:13

Anuruddha · Accepted Answer · 2018-02-08 07:42:28Z

1

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;

edited Feb 8, 2018 at 7:42

answered Feb 8, 2018 at 7:36

Anuruddha

3,2557 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G.Arima · Accepted Answer · 2018-02-08 07:07:23Z

1

Try this out:

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

answered Feb 8, 2018 at 7:07

G.Arima

1,1711 gold badge6 silver badges13 bronze badges

1 Comment

G.Arima Over a year ago

@dhie Kindly accept the answer if it helped you solve your query

Collectives™ on Stack Overflow

MySQL:How to add specified rows

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related