CODEWITHSUNDEEP
sqlsql-servercount
HGomez
HGomez

Reputation: 1534

How to count number of records per day?

I have a table in a with the following structure:

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.

At present I have the following SQL query written:

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?

Upvotes: 32

Views: 184218

Answers (11)

Thamarai T
Thamarai T

Reputation: 262

When a day among last 7 days, has no record means, the following code will list out that day with count as zero.

DECLARE @startDate DATE = GETDATE() - 6,
        @endDate DATE = GETDATE();

DECLARE @daysTable TABLE
(
OrderDate date
)

DECLARE @daysOrderTable TABLE
(
OrderDate date,
OrderCount int
)

Insert into @daysTable
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;


Insert into @daysOrderTable
select OrderDate, ISNULL((SELECT COUNT(*) AS OdrCount
                            FROM [dbo].[MyOrderTable] odr
                            WHERE CAST(odr.[CreatedDate] as date) = dt.OrderDate
                            group by CAST(odr.[CreatedDate] as date)
                        ), 0) AS OrderCount from @daysTable dt

select * from @daysOrderTable

RESULT

OrderDate     OrderCount
2022-11-22     42
2022-11-23     6
2022-11-24     34
2022-11-25     0
2022-11-26     28
2022-11-27     0
2022-11-28     22

Upvotes: 0

Ahmad Adil
Ahmad Adil

Reputation: 11

SELECT DATE_FORMAT(DateAdded, '%Y-%m-%d'), 
       COUNT(CustID) 
FROM Responses 
GROUP BY DATE_FORMAT(DateAdded, '%Y-%m-%d');

Upvotes: 1

LostNomad311
LostNomad311

Reputation: 2065

Unfortunately the best answer here IMO is a comment by @Profex on an incorrect answer , but the solution I went with is

SELECT FORMAT(DateAdded, 'yyyy-MM-dd'), count(CustID)
FROM Responses
WHERE DateAdded >= dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY FORMAT(DateAdded, 'yyyy-MM-dd')
ORDER BY FORMAT(DateAdded, 'yyyy-MM-dd')

Note that I haven't tested this SQL since I don't have the OP's DB , but this approach works well in my scenario where the date is stored to the second

The important part here is using the FORMAT(DateAdded, 'yyyy-MM-dd') method to drop the time without losing the year and month , as would happen if you used DATEPART(day, DateAdded)

Upvotes: 0

Shezan Kazi
Shezan Kazi

Reputation: 4660

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

Upvotes: 1

CodingCretin
CodingCretin

Reputation: 441

This one is like the answer above which uses the MySql DATE_FORMAT() function. I also selected just one specific week in Jan. 

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 )

Upvotes: 3

moloe
moloe

Reputation: 29

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')

Upvotes: 1

Diego
Diego

Reputation: 36176

select DateAdded, count(CustID)
from Responses
WHERE DateAdded >=dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY DateAdded

Upvotes: 38

Bruno Costa
Bruno Costa

Reputation: 2720

SELECT DateAdded, COUNT(1) AS NUMBERADDBYDAY
FROM Responses
WHERE DateAdded >= dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY DateAdded

Upvotes: 3

juergen d
juergen d

Reputation: 204924

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

Upvotes: 0

Brian
Brian

Reputation: 2239

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.

Upvotes: 0

triclosan
triclosan

Reputation: 5724

select DateAdded, count(CustID)
from tbl
group by DateAdded

about 7-days interval it's DB-depending question

Upvotes: 11

Related Questions