CODEWITHSUNDEEP
phpmysql
TheRob
TheRob

Reputation: 133

multiple sum in mysql

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

Upvotes: 2

Views: 214

Answers (4)

TheRob
TheRob

Reputation: 133

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?

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 2

Mahfuzul Alam
Mahfuzul Alam

Reputation: 3157

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

Upvotes: 0

Related Questions