SQL要为我的订户选择唯一域计数?
问题描述:
我试图找出我从每个域都有用户数量,雅虎,谷歌,AOL的....SQL要为我的订户选择唯一域计数?
这里是我想出了但不分组,因为我认为这将
select SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) -PATINDEX('%@%', EmailAddress)) As [Domain], COUNT(SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) - PATINDEX('%@%', EmailAddress))) As [Count]
from Subscribers
GROUP BY EmailAddress
结果是这样的
yahoo.com 1
gmail.com 1
yahoo.com 1
我想要的东西更多类似这样的
yahoo.com 2
gmail.com 1
答
问题是,您正在按emailaddress
分组,这不是domain
的值。如果电子邮件地址包含[email protected]
或[email protected]
,因为这些地址是唯一的,所以当您将组计数分隔时。
如果你想组由域值,那么你需要GROUP BY
你计算formala域:
select SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) -PATINDEX('%@%', EmailAddress)) As [Domain],
COUNT(SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) - PATINDEX('%@%', EmailAddress))) As [Count]
from Subscribers
GROUP BY SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) -PATINDEX('%@%', EmailAddress));
或者你可以通过domain
别名使用子查询,然后组:
select domain,
count(*) [count]
from
(
select SUBSTRING(EmailAddress, PATINDEX('%@%', EmailAddress) +1, LEN(EmailAddress) -PATINDEX('%@%', EmailAddress)) As [Domain]
from Subscribers
) d
group by domain;
谢谢,我知道这是类似的东西。我试图按域进行分组,所以至少我在正确的轨道上,我不知道我必须添加公式。我会在12分钟内回答... – user1735894 2013-05-01 16:54:57