Count values with specific data in MYSQL

Question

I have a huge list of values that I need to count, the example is, I have 4000 barcodes and those 4000, 2000 are marked in the column out of stock with a yes.

Better explanations

In total barcode counting I use this code

<?php
$result=mysql_query("SELECT count(*) as total from n_emails");
$data=mysql_fetch_assoc($result);
echo "Total de emails ativos: ";
echo $data['total'];
echo "Total de emails inativos: ";
echo $data['total'];
?>

Could I specify in this code the table count only for those who are marked as yes in the column out of stock?

To help further, the structure of the table is like this:

I’m trying to is to modify the query that is in php just so you can tell emails who are with a s in the column active.

Some light on how I could do that?

Answer 1

Follows a query that counts the assets, the non-active and the total in one query, returning everything in the same line, to simplify the use in PHP:

_{(line breaks for easy reading)}

SELECT
   SUM( IF( `ativo`='s', 1, 0 ) ) AS ativos,
   SUM( IF( `ativo`='s', 0, 1 ) ) AS inativos,
   COUNT(*)                       AS total
FROM
   tabela

See working on SQL Fiddle.

The columns will be returned in ativos, inativos and total, simplifying obtaining the data with the correct names.

Obviously there is no need to return the three columns, two would be enough to get all the data, but I left complete the query to illustrate.

Answer 2

Well, basically what you need to use is a Where in your query, try the following code:

$result=mysql_query("SELECT count(*) as total from n_emails WHERE ativo='s'");

If you use with group by can also work, see:

$result=mysql_query("SELECT ativo, count(*) as total from n_emails GROUP BY ativo");

This code will return two lines, one with the amount of assets and the other with the amount of inactive, then just you develop the logic for the display.

I hope I’ve helped.