How to get the total and individual percentage per field in ASP Classico

Question

I have the following scenario:

My DB (in Mysql) has 1 million users, and this are divided into 3 categories of registration: Male, Female and Transgender. The goal is to categorize as follows.

Example:

Total 1.400.005 Users

Male - 30%

female - 50%

Trans - 20%

On the table it’s like this

01 | Maria da silva | 24 years old | Female
02 | Joao da silva | 20 years old | Male
03 | Vanusa da silva | 25 years old | Transsexual

Answer 1

Assuming the table name = table name and the column name is = sex, we can do so:

select
    sexo,
    count(*) as totalSexo,
    count(*) / (select count(*) from tabelax) as Percentagem
from tabelax
group by sexo

Example table:

Upshot:

If you want the results in percentage form rounded to 2 decimal places this row

count(*) / (select count(*) from tabelax) as Percentagem

for

round((count(*) / (select count(*) from tabelax)*100),2) as `%`

Or if you’d rather not % as an alias for the column, by

round((count(*) / (select count(*) from tabelax)*100),2) as Percentagem

Upshot:

Round - Returns a rounded numerical value for the specified length or accuracy. This function takes two parameters: the first of them is the number to be formatted, and the second is the number of decimals that the user wants the value to display. In the default of the second parameter, the command rounds the value to an integer (no decimal places).

Answer 2

ASP I’m a layman, but you’re on the right track, like in the PHP I don’t think there’s much difference.

What you need to do is this, in your sql query do something like:

$sql = 'SELECT COUNT(sexo) as total_sex, COUNT(id) as total FROM usuarios GROUP BY sexo';

GROUP BY sex, it groups all lines containing the same data.

Example in PHP

Access the site https://www.tutorialspoint.com/php_mysql_online.php and put the code down:

<?php
$driver = 'mysql';
$database = "dbname=CODINGGROUND";
$dsn = "$driver:host=localhost;unix_socket=/home/cg/mysql/mysql.sock;$database";

$username = 'root';
$password = 'root';

try {
   $conn = new PDO($dsn, $username, $password);
   echo "<h2>Database CODINGGROUND Connected<h2>";
}catch(PDOException $e){
   echo "<h1>" . $e->getMessage() . "</h1>";
}
$sql = 'SELECT COUNT(sex) as total_sex,COUNT(id) as total, sex FROM users GROUP BY sex';
$stmt = $conn->prepare($sql);
$stmt->execute();

$TotalRegistros = 0;
$Total['F'] = 0;
$Total['M'] = 0;

echo "<pre>";
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
while($row = array_shift($rows)){
    $TotalRegistros = ($TotalRegistros + $row['total']);
    $Total[$row['sex']] = $row['total_sex'];
    echo "Total de " . $row['sex'] . ": " .$row['total_sex'];
    echo "<br>";
}

echo "Registros: " . $TotalRegistros . "<br>";
$PorcentagemMulheres = ( $Total['F'] / $TotalRegistros ) * 100;
$PorcentagemHomens = ( $Total['M'] / $TotalRegistros ) * 100;
echo "Porcentagem de Mulheres: " . number_format( $PorcentagemMulheres, 2 ) . '%' . "<br>";
echo "Porcentagem de Homens: " . number_format( $PorcentagemHomens, 2 ) . '%' . "<br>";
?>

Answer 3

Count the number of each gender and the overall total of records:

set rs=conn.execute("select count(*) as num_total,count(case when gen = 'masculino' then gen end) as num_masc,count(case when gen = 'feminino' then gen end) as num_femi,count(case when gen = 'trans' then gen end) as num_trans from tabela")
num_masc = rs("num_masc") 'total de masculino
num_femi = rs("num_femi") 'total de feminino
num_trans = rs("num_trans") 'total de trans
num_total = rs("num_total") 'total de registros
rs.close:set rs=nothing

With this information, you just need to apply the percentage to HTML:

    Total: <%=formatnumber(num_total,0)%>
   <br>
    Total masculino: <%=num_masc%> (<%=formatnumber((cdbl(num_masc)*100)/cdbl(num_a),2)%>%)<br>
    Total feminino: <%=num_femi%> (<%=formatnumber((cdbl(num_femi)*100)/cdbl(num_a),2)%>%)<br>
    Total trans: <%=num_trans%> (<%=formatnumber((cdbl(num_trans)*100)/cdbl(num_a),2)%>%)

Obs.: The "2" in formatnumber will display 2 decimals after comma. You can change if you want to show more or less decimals.