multiple sum in mysql

Question

I have a table structure like this:

-----------------------------
|Branch   |Cluster   |Point |
-----------------------------
|branch a |Cluster 1 |1000  |
-----------------------------
|branch a |Cluster 2 |2000  |
-----------------------------
|branch b |Cluster 3 |1000  |
-----------------------------
|branch b |Cluster 4 |1000  |
-----------------------------

I would like to select the total of each branch. So my output would be like

-----------------------------
|Branch   |Cluster   |Point |
-----------------------------
|branch a |Cluster 1 |1000  |
-----------------------------
|branch a |Cluster 2 |2000  |
-----------------------------
|Total branch a      |3000  |
-----------------------------
|branch b |Cluster 4 |1000  |
-----------------------------
|branch b |Cluster 3 |1000  |
-----------------------------
|Total branch b      |2000  |
-----------------------------

ScaisEdge · Accepted Answer · 2016-11-06 10:52:55Z

2

A simple sql way is use union and order by (with limitation taht the order by need a proper format of content for total)

otherwise you should format the result server side

select Branch, Cluster, Point
from my_table 
union all
select concat(Branch, ' Total ' , null, sum(Point)
from my_table 
order by Branch

answered Nov 6, 2016 at 10:52

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2016-11-06 11:34:22Z

2

(
    SELECT Branch, Cluster, Point
    FROM yourTable
)
UNION ALL
(
    SELECT Branch, 'Total' AS Cluster, SUM(Point) AS Point
    FROM yourTable
    GROUP BY Branch
)
ORDER BY Branch, Cluster

This assumes that all the data in the Cluster column really begins with the letter 'C'.

edited Nov 6, 2016 at 11:34

answered Nov 6, 2016 at 10:48

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Mahfuzul Alam · Accepted Answer · 2016-11-06 10:49:43Z

0

SELECT Branch, SUM(POINT) AS Total
FROM Table_Name
GROUP BY Branch ASC

answered Nov 6, 2016 at 10:49

Mahfuzul Alam

3,17716 silver badges15 bronze badges

Comments

TheRob · Accepted Answer · 2016-11-06 14:42:03Z

0

i have found the answer, just like this

    SELECT branch,ifnull(cluster,'Total --->')AS cluster,COUNT(rs) AS point 
FROM sefiia_registered_outlet_rs GROUP BY branch,cluster WITH ROLLUP

now i get new problame because i want to add more column. i use this query

    SELECT branch,ifnull(cluster,'Total --->')AS cluster,COUNT(rs) AS point, 
    (SELECT SUM(rp) from full_mcd 
    where full_mcd.cluster=sefiia_registered_outlet_rs.cluster GROUP BY full_mcd.cluster) 
    AS RP FROM sefiia_registered_outlet_rs GROUP BY branch,cluster WITH ROLLUP

and i get output like this

---------------------------------
|Branch   |Cluster   |Point |RP |
---------------------------------
|branch a |Cluster 1 |1000  |500|
---------------------------------
|branch a |Cluster 2 |2000  |300|
---------------------------------
|branch a |Total --->|3000  |300|
---------------------------------
|branch b |Cluster 4 |1000  |100|
---------------------------------
|branch b |Cluster 3 |1000  |200|
---------------------------------
|branch b |Total --->|2000  |200|
---------------------------------

the result was good except row total ---> for RP column. how can i fix this?

edited Nov 6, 2016 at 14:42

answered Nov 6, 2016 at 13:08

TheRob

1332 silver badges10 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

Rollup won't work for your problem AFAIK because you want a special summary per group, and rollup gives you a summary total over all groups.

Collectives™ on Stack Overflow

multiple sum in mysql

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related