CODEWITHSUNDEEP
mysql
Fresher
Fresher

Reputation: 47

Calculate Percentage in mysql for each site

I have a table structure with columns Site, Item Amount,Item Weight. I want to calculate the percentage for each site. I have tried this query but getting wrong % value. Please help. 

Site |  Item Amount |  Item Weight 
 A   |    1000      |    50
 B   |    800       |    35
 C   |    1500      |    65 

 Select Site, sum(Item Amount) As Amount, sum(Item Weight)
 AS MT,concat(round((Item Amount/sum(Item Amount)*100),2),%)
 AS percentage from 'table1' group by site.

Upvotes: 1

Views: 62

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

Instead of using a subquery to calculate the total sum, you can use a CROSS JOIN instead.

SELECT t1.Site, sum(t1.`Item Amount`) As Amount, sum(t1.`Item Weight`) AS MT,
    SUM(t1.`Item Amount`) / t2.itemSum * 100 AS `Item percentage`,
    SUM(t1.`Item Weight`) / t3.weightSum * 100 AS `Weight percentage`
FROM table1 t1 CROSS JOIN
(SELECT SUM(`Item Amount`) AS itemSum FROM table1) t2 CROSS JOIN
(SELECT SUM(`Item Weight`) AS weightSum FROM table1) t3
GROUP BY t1.Site

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37099

From what I understand, you need percent of each site as compared to the total in the database:

select
  site,
  sum(`Item Amount`) as TotalItemAmount,
  sum(`Item Amount`)*100/(select sum(`Item Amount`) from table1) as ItemAmountPercent,
  sum(`Item Weight`) as TotalItemWeight,
  sum(`Item Weight`)*100/(select sum(`Item Weight`) from table1) as ItemWeightPercent
from table1
group by site

This should do it for you, I believe. This query groups the sites. For each site, it totals up item amount and divides it by total of item amount in the entire table. Same goes with item weight.

Upvotes: 1

Related Questions