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

3

I have the following scenario:

My DB (in MySQL) has 1 million users, of which it is divided into 3 categories of register: Male, Female and Transsexual. The goal is to categorize as follows.

Example:

Total 1,400,005 Users

Male - 30%

Female - 50%

Trans-20%

The table is like this

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

    
asked by anonymous 13.08.2017 / 18:41

3 answers

0

Count the number of each genre and the grand total of the 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 only have to apply the percentage in 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)%>%)

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

    
14.08.2017 / 03:06
2

Assuming that the table name = tablex and the column name is = sex, we can do this:

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

Sample table:

Result:

Ifyouwanttheresultsasapercentageroundedto2decimalplacesreplacethisline

count(*)/(selectcount(*)fromtabelax)asPercentagem

by

round((count(*)/(selectcount(*)fromtabelax)*100),2)as'%'

Orifyouprefernottouse%asacolumnalias,by

round((count(*)/(selectcount(*)fromtabelax)*100),2)asPercentagem

Result:

  

Round - Returns a numeric value, rounded, to the specified length or precision.   This function receives two parameters: the first one is the number to be formatted, and the second is the number of decimal places the user wants the value to display. In the default of the second parameter, the command rounds the value to an integer (without decimals).

    
14.08.2017 / 16:17
1

ASP I'm a layman, but you're on the right track, just like in PHP

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

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

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

PHP Exceptions

Visit the link site and place the code below:

<?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>";
?>
    
14.08.2017 / 02:38