一张表统sql语句计....在线等.

[cqhweb]

现有一表有数据如下:

RECEIVESMS_ID NOTES_ID MOBILE IS_NEW
83 bailaxiusx 13734039384 0
97 bailaxiusx 13734039384 0
101 bailaxiusx 13734039384 0
102 bailaxiusx 13734039384 1
84 SXADM 13734039384 0
98 bailaxiusx 13934116969 0
99 bailaxiusx 13934116969 1

提示:我用sql

SELECT NOTES_ID,MOBILE,COUNT(*) AS   total  FROM tb WHERE NOTES_ID ='bailaxiusx' GROUP BY NOTES_ID,MOBILE 

仅得出这样的数据..
NOTES_ID MOBILE total
bailaxiusx 13734039384 4
bailaxiusx 13934116969 2


------------------希望输出以下结果-------

NOTES_ID MOBILE total IS_NEW_0 IS_NEW_1
bailaxiusx 13734039384 4 3 1
bailaxiusx 13934116969 2 1 1

谢谢各位仁兄指点...sql怎么写?

[liangCK]

SELECT NOTES_ID,MOBILE,COUNT(*) AS total ,
    SUM(CASE WHEN IS_NEW=0 THEN 1 ELSE 0 END) AS IS_NEW_0,
    SUM(CASE WHEN IS_NEW=1 THEN 1 ELSE 0 END) AS IS_NEW_1
FROM tb 
WHERE NOTES_ID ='bailaxiusx' 
GROUP BY NOTES_ID,MOBILE

[cqhweb]

IS_NEW 是varchar类型的 不能换行

SUM(CASE WHEN IS_NEW=0

报:
conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.

[王向飞]

SELECT NOTES_ID,MOBILE,COUNT(*) AS   total, IS_NEW_0 =SUM(CASE WHEN IS_NEW=0 THEN 1 ELSE 0 END),IS_NEW_1 =SUM(CASE WHEN IS_NEW=1 THEN 1 ELSE 0 END) FROM tb WHERE NOTES_ID ='bailaxiusx' GROUP BY NOTES_ID,MOBILE 

[cqhweb]

不行..要转换数据类型..