Count values with specific data in MYSQL

Question

Count values with specific data in MYSQL

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

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

Better explanations

In the total bar code count 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'];
?>

I would like to specify in this code the table count only for who is marked as yes in the column without stock ?

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

I'm trying to modify the query that is in php so that it counts only emails with an s in column < in> active .

Any light on how you could do this?

php mysql query

asked by anonymous 02.05.2016 / 18:05

2 answers

3

A query that counts assets, non-assets, and totals in one query, returning everything on the same line, to simplify 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 .

Columns will be returned in ativos , inativos and total , simplifying getting the data already 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 the query to illustrate.

02.05.2016 / 18:51

Create custom freight module with settings table [closed] Subselect in Oracle database

score 2 · Accepted Answer

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 group by it may 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 another with the amount of inactive, then you just have to develop the logic for the display.

I hope I have helped.