How could I correctly write PostgreSQL query for percentages?-CodePudding

i have tables like this

 companies                 users
id|   name              id |  company_id|  name
1 | AIRBUS             101 |    2       | Martin
2 | BOEING             102 |    1       | Ray
3 | CHRYSLER           103 |    3       | Richard

and I have to get as results : Company/count of users/percentage of users.

I tried that

SELECT companies.name as Societe
, COUNT(users.id) as nbre_utilisateurs
, nbre_utilisateurs*100/(SELECT SUM (nbre_utilisateurs) as pourcentage 
FROM users
JOIN companies ON companies.id=users.company_id
GROUP BY Societe
ORDER BY nbre_utilisateurs DESC

and get as response :

Query Error: error: syntax error at or near ";"

Then my question is : What ";" ? There's not any ";" here ... I've been trying many other syntaxes but did not manage to get Company/count of users/percentage of users.

I have to find 2 different ways of making it, and determine how to make it the fastest. I'm really new to PostgreSQL and ask for your kind help :)

CodePudding user response：

As others have pointed out, you have a missing ) in your query which is the source of the error. Even if you fix that your query will still not work, because you are referencing a calculated column nbre_utilisateurs within the same SELECT statement. There are a few ways of solving this as outlined in this Stackoverflow answer.

I'd personally re-write the query to calculate the users per company in a CTE, and then have a separate query to calculate the market share percentage.

-- CTE for calculating users per company
with anon_1 as (
    SELECT companies.name as Societe,
        COUNT(users.id) as nbre_utilisateurs
    FROM users
        JOIN companies ON companies.id = users.company_id
    GROUP BY companies.name
)
-- Final query to get company, number of users, market share
SELECT anon_1.Societe,
    anon_1.nbre_utilisateurs,
    (
        anon_1.nbre_utilisateurs * 100 / SUM (anon_1.nbre_utilisateurs)
    ) as pourcentage
from anon_1
ORDER BY anon_1.nbre_utilisateurs DESC

CodePudding user response：

this is wrong nbre_utilisateurs*100/(SELECT SUM (nbre_utilisateurs) as pourcentage

SELECT companies.name as Societe
, COUNT(users.id) as nbre_utilisateurs
, COUNT(users.id) *100/(SELECT count(*) from users) as pourcentage 
FROM users
JOIN companies ON companies.id=users.company_id
GROUP BY Societe
ORDER BY nbre_utilisateurs DESC

also you can use window function :

SELECT companies.name as Societe
, COUNT(users.id) as nbre_utilisateurs
, COUNT(users.id) *100/ sum(count(users.id)) over () as pourcentage 
FROM users
JOIN companies ON companies.id=users.company_id
GROUP BY Societe
ORDER BY nbre_utilisateurs DESC