Getting count of different records from single table-MySQL

Question

I am having the table named tbl_sales and this table contains the fields active and is_featured. Now i am trying to get the count of the records where active=0, active=1, is_featured=1 and is_featured=0. How can i get this in a single query. Can somebody show me a query to get this. I am a newbie to programming and in a self learning process. 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

The IF(active=0,1,0) says "if active is 1, return a 1. otherwise, return a 0".

The SUM around it adds up all the numbers, which are 0 if non-active and 1 if active (etc).

Answer 2

This 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 provide the count for each group. Since there is a having clause, it will restrict to have rows only columns having values 0 or 1.