CODEWITHSUNDEEP
mysql
menawi
menawi

Reputation: 399

Calculating the Growth Percentage between two periods

I have a database with a single table of complains and the date they were submitted. What I have to do is, given the start and end dates of two periods, I have to calculate the percentage of growth between them.

For example:

To calculate growth:

The information I have to extract from that report that is claims have grown 100% between Q1 and Q2

This is what I came up with:

SELECT 
  (SELECT COUNT(id) FROM complaints WHERE submit_date >= start_date_period_1 AND submit_date <= end_date_period_1) AS q1_claims,
  (SELECT COUNT(id) FROM complaints WHERE submit_date >= start_date_period_2 AND submit_date <= end_date_period_2) AS q2_claims,
  (SELECT ((q2_claims - q1_claims)/q2_claims * 100) AS 'Percentage Growth')
FROM complaints;

but does not show the output in the correct form. It shows a record for every date in the given period. How can i fix the query?

Upvotes: 6

Views: 3041

Answers (1)

Horia
Horia

Reputation: 1612

In your original query, you were mixing aggregate (i.e. COUNT) and non-aggregate columns, forcing MySQL to give you a result set containing a record for every row. Try using this query instead:

SELECT 
  ((q2.claims - q1.claims)/q2.claims * 100) AS 'Percentage Growth'
FROM 
    (SELECT COUNT(id) AS claims FROM complaints WHERE submit_date >= start_date_period_1 AND submit_date <= end_date_period_1) AS q1,
    (SELECT COUNT(id) AS claims FROM complaints WHERE submit_date >= start_date_period_2 AND submit_date <= end_date_period_2) AS q2

Upvotes: 7

Related Questions