CODEWITHSUNDEEP
mysql
Fresher
Fresher

Reputation: 47

Calculate growth percentage between previous year and current year

I want to calculate the growth percentage over the previous year.

Table structure:

CustomerName | Sum_1415 | Sum_1516

I have tried this query:

select CustomerName, SUM(Sum_1415), SUM(Sum_1516), 
    round(sum(Sum_1516)-Sum(Sum_1415)/(select sum(Sum_1415) from dummy),2) as Percentage 
from dummy 
group by CustomerName 
order by Sum_1415 desc 
limit 15

Upvotes: 0

Views: 1339

Answers (1)

Barmar
Barmar

Reputation: 781210

Replace the subquery (select sum(Sum_1415) from dummy) with just sum(Sum_1415). Otherwise, you're calculating the ratio of the growth for each customer to the total of all revenues from the previous year, not the total for that customer.

You were also missing some parentheses inside the ROUND call, around the subtraction sum(Sum_1516)-Sum(Sum_1415).

select CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516, 
    round((sum(Sum_1516)-Sum(Sum_1415))/SUM(Sum_1415),2) as Percentage 
from dummy 
group by CustomerName 
order by Sum_1415 desc 
limit 15

I would probably refactor this into a subquery so it's not necessary to keep repeating the SUM expressions.

SELECT CustomerName, Sum_1415, Sum_1516, ROUND((Sum_1516-Sum_1415)/Sum_1415, 2) AS Percentage
FROM (SELECT CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516
      FROM dummy
      GROUP BY CustomerName
      ORDER BY Sum_1516 DESC
      LIMIT 15) AS x

Upvotes: 1

Related Questions