从具有不同条件的相同表中获取数据[关闭]

Question

I have one table name as user(id,Name,Status,joined_date).

usertable

 id | Name | status | joined_date

 -------------------------------------
 1  | TDS  |  0      |      2014-10-16 

 2  | TDS  |  1      |      2014-10-11 

 3  | VAT  |  0      |      2014-10-11 

 4  | IT   |  0      |      2014-10-11 

 5  | IT   |  1      |      2014-10-11

 6  | TDS  |  0      |      2014-10-16 

 7  | TDS  |  1      |      2014-10-30 

 8  | VAT  |  0      |      2014-11-01 

 9  | IT   |  0      |      2014-10-07 

10  | IT   |  1      |      2014-10-11 

In my result i need count of Name in current month and count of name in current month with status is one. For each name the both result in same row.

expected result

Name    |   current_month | current_month_ststus_1             

-----------------------------------------------------

  VAT   |         2       |            0 

  IT    |         4       |            1

 TDS    |         4       |            2

I am bit confuse for this query for last days.Please help me if possible. Thanks in advance

Answer 1

SELECT name,count(status) as current_month, sum(status) as current_month_status FROM usertable WHERE month(joined_date) = MONTH(CURDATE()) GROUP BY name