CODEWITHSUNDEEP
phpmysql
Paramesh Reddy
Paramesh Reddy

Reputation: 325

how to Write a query to fetch data as follows in mysql?

I have a tbl_users like this

Uid       Fname     createddate    Active(0:inactive;1:active;2:pending)

1        user1      2014-12-14     1                              
2        user2      2013-11-18     0  
3        user3      2014-12-14     1

What I want is .....

based on date how many users are in active,inactive and pending state?

array(

[0]=>array(
      [date]=>2014-12-14
      [active]=2
      [inactive]=0
      [pending]=0
     )
[1]=>array(
      [date]=>2013-11-18
      [active]=0
      [inactive]=1
      [pending]=0
     )

 )

Upvotes: 0

Views: 66

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

In Mysql the conditional sum is easy and could be expressed as

select
createddate,
sum( Active = 1 ) as `active`,
sum( Active = 0 ) as `inactive`,
sum( Active = 2 ) as `pending`
from tbl_users
group by createddate

Upvotes: 3

SMA
SMA

Reputation: 37023

Try something like:

SELECT date, COUNT(CASE WHEN Active = 0 then 1 ELSE NULL END) as "Inactive",
             COUNT(CASE WHEN Active = 1 then 1 ELSE NULL END) as "Active",
             COUNT(CASE WHEN Active = 2 then 1 ELSE NULL END) as "Pending" 
FROM tbl_users
GROUP BY date

Upvotes: 2

Related Questions