Getting the number of different records from one table-MySQL

Question

I have a table called tbl_sales , and this table contains the active and is_featured . Now I'm trying to get a record counter, where active=0 , active=1 , is_featured=1 and is_featured=0 . How can I get this in one request. Can someone show me a request to get this. I am new to programming and do-it-yourself learning. Need solutions. thanks in advance

Answer 1

Try using SUM :

 SELECT SUM(IF(active=0,1,0)) as nonActiveCount, SUM(IF(active=1,1,0)) as activeCount, SUM(IF(featured=0,1,0)) as nonfeaturedCount, SUM(IF(featured=1,1,0)) as featuredCount FROM myTable 

IF(active=0,1,0) says: "If 1 is active, return 1. otherwise return 0."

SUM around it are added all the numbers that are 0 if they are inactive, and 1 if they are active (etc.).

Answer 2

It will do.

 select active, is_featured, count(*) from tbl_sales group by active, is_featured having (active=0 or active=1) and (is_featured=0 or is_featured=1); 

Update:

This SQL will group all rows with all four combinations.

 active=0; is_featured=0 active=0; is_featured=1 active=1; is_featured=0 active=1; is_featured=1 

and indicate the quantity for each group. Since there is a sentence, it restricts the presence of rows to only columns that have values ​​0 or 1.

Answer 3

for instance

 SELECT cnt.sq1 AS active, cnt.sq2 AS inactive, cnt.sq3 AS featured, cnt.sq4 AS not_featured FROM ( SELECT COUNT(*) AS cnt FROM tbl_sales WHERE active = 1 ) AS sq1 JOIN FROM ( SELECT COUNT(*) AS cnt FROM tbl_sales WHERE active = 0 ) AS sq2 JOIN FROM ( SELECT COUNT(*) AS cnt FROM tbl_sales WHERE is_featured= 1 ) AS sq3 JOIN FROM ( SELECT COUNT(*) AS cnt FROM tbl_sales WHERE is_featured= 0 ) AS sq4 

Answer 4

 SELECT active,is_featured,count(*) FROM tbl_sales GROUP By active,is_featured 

sample output:

 +--------+-------------+----------+ | active | is_featured | count(*) | +--------+-------------+----------+ | 0 | 0 | 7 | | 0 | 1 | 6 | | 1 | 0 | 204 | | 1 | 1 | 66 | +--------+-------------+----------+ 

Answer 5

When you look at mySQL GROUP BY modifiers , you will find there an example with several GROUP BY columns (this is the only one official example that I found):

 mysql> SELECT year, country, product, SUM(profit) -> FROM sales -> GROUP BY year, country, product; +------+---------+------------+-------------+ | year | country | product | SUM(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2001 | Finland | Phone | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | +------+---------+------------+-------------+ 

See how WITH ROLLUP works (you can and probably find it useful).

So, create your request as follows:

 SELECT active, is_featured, COUNT(*) as cnt FROM tbl_sales GROUP BY active, is_featured WITH ROLLUP 

You always need to return 4 lines that would look like this:

 +--------+--------------+-----+ | active | is_feautured | cnt | +--------+--------------+-----+ | 1 | 1 | 5 | | 1 | 0 | 8 | | 0 | 1 | 0 | | 0 | 0 | 7 | +--------+--------------+-----+ 

And than with a simple loop (example in php, do it in what you need:

 // Get results from DB $sql = 'SELECT active, is_featured, COUNT(*) as cnt FROM tbl_sales GROUP BY active, is_featured WITH ROLLUP'; $q = mysql_query( $sql); // Initialize values, two array keys 0: value 0, 1: value 1 $active = array( 0, 0); $is_featured = array( 0, 0); while( $row = mysql_fetch_assoc( $q)){ $active[ $row['active']] += $row['cnt']; $is_featured[ $row['is_feautured']] += $row['cnt']; }