Calculating the Growth Percentage between two periods

Question

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:

• Q1 (January - March) Claims = 200

• Q2 (April - June) Claims = 400

To calculate growth:

• (Present - Past) / Past * 100

• Growth percentage = (400 - 200) / 200 * 100 = 100% 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?

Answer 1

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