How to calculate percentage for each type in SQL

Question

I did some digging but couldn't find a question similar to mine...

I want to make a function that calculates the percentage of people with a specific education. For example off all the possible educations (in my table) how many are pilots how many are engineers, etc.

I have two tables: table Users has a foreign key to table Education. The goal is to return a table in which you have stated all the possible educations and the percentage of people with it.

I got the numbers with

SELECT Educations.education, COUNT(Users.idEducation) AS numberOfEducations
FROM Educations
LEFT JOIN Users ON (Educations.idEducation= Users.idEducation)
GROUP BY Educations.education;

And number of all the possible educations with

SELECT COUNT(idEducation) AS allTypes FROM Educations;

Now what I would need to do and failed on all fronts is to calculate each educations percentage and output something in the form of:

| Education | Percentage |
| Pilot     |     5%     |
| Engineer  |    10%     |
| Sales     |    25%     |
| Banker    |     8%     |

Answer 1

You can use ANSI standard window functions in most databases:

SELECT e.education, COUNT(u.idEducation) AS numberOfEducations,
       COUNT(u.idEducation)*1.0 / SUM(COUNT(u.idEducation)) OVER () as ratio
FROM Educations e LEFT JOIN
     Users u
     ON e.idEducation = u.idEducation)
GROUP BY e.education;

The * 1.0 is because some databases do integer division -- that is 1/2 is 0 rather than 0.5.