DEV Community

Sven Schannak
Sven Schannak

Posted on

MySQL calculate percentages

Information is only useful, if you view it in a context. In data analysis you should avoid to only look at absolute numbers. For example you could just get the number of all users that have signed up in the previous month:

SELECT COUNT(*) as has_orderd FROM users WHERE users.has_ordered = true AND YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
Enter fullscreen mode Exit fullscreen mode

Result:

has_ordered 450
Enter fullscreen mode Exit fullscreen mode

But this number only gives you a certain amount of useful information. So you should look at that data, compared to all users that signed up the previous month:

SELECT COUNT(*) as total FROM users WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
Enter fullscreen mode Exit fullscreen mode

Result:

total 1100
Enter fullscreen mode Exit fullscreen mode

If you want both data points next to each other, you can do the following:

SELECT SUM(users.has_ordered = true) as user_with_orders, COUNT(*) as total FROM users WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
Enter fullscreen mode Exit fullscreen mode

Result:

user_with_orders total 450 1100
Enter fullscreen mode Exit fullscreen mode

And to calculate the percentage of all users that have signed up the last month, you can just do:

SELECT SUM(users.has_ordered = true) as user_with_orders, COUNT(*) as total, ((SUM(users.has_ordered = true)/COUNT(*)) * 100) as percentage FROM users WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
Enter fullscreen mode Exit fullscreen mode

Result:

user_with_orders total percentage 450 1100 40.9
Enter fullscreen mode Exit fullscreen mode

Top comments (0)