SQL 区分和计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/913302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL distinct and count
提问by Tetraneutron
I have a query where I want to get distinct dates, the phone numbers associated with those dates, and a count of the phone numbers per date.
我有一个查询，我想在何处获取不同的日期、与这些日期关联的电话号码以及每个日期的电话号码计数。
For example, I have a database with dates and phone numbers and I want the result to be
例如，我有一个包含日期和电话号码的数据库，我希望结果是
9/2005 5554446666 3
9/2005 4445556666 1
10/2005 1112223333 1
11/2005 2223334444 2
I can get the dates and the counts with this query:
我可以使用此查询获取日期和计数：
SELECT DISTINCT date, count(phone) AS count
FROM calls
GROUP BY date
What I can't seem to get is the phone number the 'count' is counting. I think I need some sort of aggregate function to get a single instance of the list of unique values but First() and a few others only throw a SQL error. Do I need a subquery?
我似乎无法得到的是“计数”正在计算的电话号码。我想我需要某种聚合函数来获取唯一值列表的单个实例，但 First() 和其他一些只抛出 SQL 错误。我需要一个子查询吗？
回答by Tetraneutron
SELECT date, PhoneNumber, count(phone) AS count
FROM calls
GROUP BY date, PhoneNumber
should do it I think
我认为应该这样做
回答by Kibbee
You may want to try something like
你可能想尝试类似的东西
SELECT Date, Phone, Count(*) As Count From Calls GROUP BY Date, Phone
This will give you a tally of each phone number on each date, with how many times that number appeared on that date.
这将为您提供每个日期的每个电话号码的计数，以及该号码在该日期出现的次数。
回答by stuartd
SELECT date, phone, count(phone) AS count FROM calls GROUP BY date, phone
(You don't need DISTINCT with a GROUP BY.)
（您不需要 DISTINCT 与 GROUP BY。）
回答by joshcomley
That's because you're grouping by datenot by phone.
那是因为您是按date而不是按phone分组。
In one grouping of a date there may be three different numbers and it's impossible for SQL to know which one you want.
在一组日期中可能有三个不同的数字，并且 SQL 不可能知道您想要哪一个。
回答by Hyman Hogue
Try the Following
尝试以下
Count(phone) FROM calls
计数（电话）FROM 呼叫
Will give you the Distinct Count.
会给你不同的计数。
