How to Count Total of Multiple Type Records in a Single Query in sql

Asked

Viewed 170 times

0

Hello Today I’m making the record counts this way 

$sql1 = "SELECT COUNT(*) AS total1 FROM a_finan Where  cat = 'Ativos' ;
$resultado1 = mysql_query($sql1) or die ("Erro na consulta1");
$linha1 = mysql_fetch_assoc($resultado1);
echo $total1 = $linha1['total1'];

$sql2 = "SELECT COUNT(*) AS total2 FROM a_finan Where  cat = 'Inativos' ;
$resultado2 = mysql_query($sql2) or die ("Erro na consulta2");
$linha2 = mysql_fetch_assoc($resultado2);
echo  $total2 = $linha2['total2'];

the 1st Active Total Registration Account the 2nd total inactive records account and the third is still the same...

Would there be some way to count a single query so the code doesn’t get giant

php mysql

1 answer

2

SELECT cat, COUNT(*) as total
FROM a_finan
GROUP BY cat

This will bring the total records grouped by category 'cat'.

  • Okay I think that’s right Could just explain to me how to show the records separated by cat?

  • You’ll get the answer the same way you did before... The difference now is that it will return more than one line so you will have to go through the array. Something like this: $result = mysql_query($sql) or die ("Query error"); $line = mysql_fetch_assoc($result); while ($Row = mysql_fetch_assoc($line)) { echo $Row["total"] . '<br>'; }

  • Hello Ok That’s right. You would know how to pass each result to a variable

  • I don’t know exactly what you need, but if inside the while() you use $resultado[] = $row["total"]. At the end of the run, you will have the values inside this array. Then just use $result[0], $result[1], $result[2]....

  • I don’t think it works for what I was looking for. each result belongs to a category = cat it returns me type 10 , 22, 15 as I will know to whom this values belong

  • Yes, it works... this query will return you an array:[0] 'cat' => 'Active', 'total' => 10 | [1] 'cat' => 'Inactive', 'total' => 22 | [2] 'cat' => 'bla bla bla'. 'total' => 15.... You will have exactly each category and the total grouped... * I know the question is old, but it can be useful to other people who come here with the same doubt.

Show 1 more comment

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.