calculer pourcentage mysql 1

calculer pourcentage mysql

SET @total = (
SELECT COUNT(id_gest_dossier)
FROM t_users_dossiers);
SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/@total*100,3) AS '%'
FROM t_users_dossiers
GROUP BY id_user;

Here is what the above code is Doing:
1. It creates a temporary table called @total.
2. It then populates the temporary table with the total number of rows in the t_users_dossiers table.
3. It then selects the id_user, the count of the id_gest_dossier, and the percentage of the total number of rows in the t_users_dossiers table.

The result of the above query is:

id_user COUNT(id_gest_dossier) %
1 2 0.667
2 1 0.333

The above result is what I want, but I want to do it in one query.

I tried the following query:

SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/(SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)*100,3) AS ‘%’
FROM t_users_dossiers
GROUP BY id_user;

But I get the following error:

#1242 – Subquery returns more than 1 row

I also tried the following query:

SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/(SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)*100,3) AS ‘%’
FROM t_users_dossiers
GROUP BY id_user
HAVING COUNT(id_gest_dossier) = (SELECT COUNT(id_gest_dossier) FROM t_users_dossiers);

But I get the following error:

#1054 – Unknown column ‘id_gest_dossier’ in ‘having clause’

I also tried the following query:

SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/(SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)*100,3) AS ‘%’
FROM t_users_dossiers
GROUP BY id_user
HAVING COUNT(id_gest_dossier) = (SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)
ORDER BY id_user;

But I get the following error:

#1054 – Unknown column ‘id_gest_dossier’ in ‘having clause’

I also tried the following query:

SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/(SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)*100,3) AS ‘%’
FROM t_users_dossiers
GROUP BY id_user
HAVING COUNT(id_gest_dossier) = (SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)
ORDER BY id_user;

But I get the following error:

#1054 – Unknown column ‘id_gest_dossier’ in ‘having clause’

I also tried the following query:

SELECT id_user,
COUNT(id_gest_dossier),
ROUND(COUNT(id_gest_dossier)/(SELECT COUNT(id_gest_dossier) FROM t_users_dossiers)*100,3) AS ‘%’
FROM t_users_

Similar Posts