求一个sql语句！！着急！！在线等！！

[peipei160509]

问一个问题：
TABLE1
---------------------
id |email
---------
1 |[e-mail]
2 |[e-mail]
3 |[e-mail]
4 |[e-mail]
5 |[e-mail]
6 |[e-mail]

如何用select语句得到这样的
---------------
id|type |number
1 |msn |3
2 |gmail |2
3 |hotmail|1

[gzmhero]

select type,count(1) from (select SUBSTRING(email,CHARINDEX('@',mail)+1,CHARINDEX('.',mail)-CHARINDEX('@',mail)-1) type from table1) a
group by type
order by count(1) desc

[peipei160509]

SELECT
SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
) ,
count(SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
))
FROM Tabcustomer
group by (
SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
)
)