SQL 如何计算每天的记录数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9229862/
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
How to count number of records per day?
提问by HGomez
I have a table in a with the following structure:
我在 a 中有一个表，其结构如下：
CustID --- DateAdded ---
396 2012-02-09
396 2012-02-09
396 2012-02-08
396 2012-02-07
396 2012-02-07
396 2012-02-07
396 2012-02-06
396 2012-02-06
I would like to know how I can count the number of records per day, for the last 7 days in SQL and then return this as an integer.
我想知道如何在 SQL 中计算过去 7 天每天的记录数，然后将其作为整数返回。
At present I have the following SQL query written:
目前我编写了以下 SQL 查询：
SELECT *
FROM Responses
WHERE DateAdded >= dateadd(day, datediff(day, 0, GetDate()) - 7, 0)
RETURN
However this only returns all entries for the past 7 days. How can I count the records per day for the last 7 days?
但是，这只会返回过去 7 天的所有条目。我如何计算过去 7 天每天的记录？
回答by Diego
select DateAdded, count(CustID)
from Responses
WHERE DateAdded >=dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY DateAdded
回答by triclosan
select DateAdded, count(CustID)
from tbl
group by DateAdded
about 7-days interval it's DB-depending question
大约 7 天的间隔，这是依赖于数据库的问题
回答by Bruno Costa
SELECT DateAdded, COUNT(1) AS NUMBERADDBYDAY
FROM Responses
WHERE DateAdded >= dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY DateAdded
回答by CodingCretin
This one is like the answer above which uses the MySql DATE_FORMAT() function. I also selected just one specific week in Jan.
这就像上面使用 MySql DATE_FORMAT() 函数的答案。我还只选择了 1 月的一个特定周。
SELECT
DatePart(day, DateAdded) AS date,
COUNT(entryhash) AS count
FROM Responses
where DateAdded > '2020-01-25' and DateAdded < '2020-02-01'
GROUP BY
DatePart(day, DateAdded )
回答by Shezan Kazi
you could also try this:
你也可以试试这个：
SELECT DISTINCT (DATE(dateadded)) AS unique_date, COUNT(*) AS amount FROM table GROUP BY unique_date ORDER BY unique_date ASC
SELECT DISTINCT (DATE(date added)) AS unique_date, COUNT(*) AS amount FROM table GROUP BY unique_date ORDER BY unique_date ASC
回答by Brian
SELECT count(*), dateadded FROM Responses
WHERE DateAdded >=dateadd(day,datediff(day,0,GetDate())- 7,0)
group by dateadded
RETURN
This will give you a count of records for each dateadded value. Don't make the mistake of adding more columns to the select, expecting to get just one count per day. The group by clause will give you a row for every unique instance of the columns listed.
这将为您提供每个日期添加值的记录计数。不要错误地将更多列添加到选择中，期望每天只得到一个计数。group by 子句将为列出的列的每个唯一实例提供一行。
回答by juergen d
select DateAdded, count(DateAdded) as num_records
from your_table
WHERE DateAdded >=dateadd(day,datediff(day,0,GetDate())- 7,0)
group by DateAdded
order by DateAdded
回答by moloe
If your timestamp includes time, not only date, use:
如果您的时间戳包括时间，而不仅仅是日期，请使用：
SELECT DATE_FORMAT('timestamp', '%Y-%m-%d') AS date, COUNT(id) AS count FROM table GROUP BY DATE_FORMAT('timestamp', '%Y-%m-%d')
SELECT DATE_FORMAT('timestamp', '%Y-%m-%d') AS date, COUNT(id) AS count FROM table GROUP BY DATE_FORMAT('timestamp', '%Y-%m-%d')
