CODEWITHSUNDEEP
sqlsql-servert-sql
user2354956
user2354956

Reputation: 25

TSql Sum By Date

Ive tried looking at some examples but none really relate to what I am trying to do. In my table I have a column which has the date, and what I would like to do is add up the records by their date. For example add all the records on March 12, 13, 14 etc. So each day should have its date and a counted sum. How would I apply that?

SELECT DTTM
FROM 
[Audits].[dbo].[Miscount]
Group by DTTM
Order by DTTM desc

Cheers!

Upvotes: 0

Views: 2156

Answers (2)

Jodrell
Jodrell

Reputation: 35746

If DTTM is a DateTime type then

SELECT
            CAST([DTTM] AS Date) [Date],
            COUNT(*) [Total]
    FROM 
            [Audits].[dbo].[Miscount]
    GROUP BY
            CAST([DTTM] AS Date)
    ORDER BY
            1 DESC

would do. Heres some fiddle to demonstrate.

Upvotes: 0

Robert McKee
Robert McKee

Reputation: 21477

If you want the number of each records for each day:

SELECT DTTM,COUNT(*) AS Total
FROM 
[Audits].[dbo].[Miscount]
Group by DTTM
Order by DTTM desc

Or if you want a sum of a field on each record:

SELECT DTTM,SUM(field1) AS Sum
FROM 
[Audits].[dbo].[Miscount]
Group by DTTM
Order by DTTM desc

Or if DTTM is a datetime then you can use:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, DTTM)) AS DTTM,COUNT(*) AS Total
FROM 
[Audits].[dbo].[Miscount]
Group by DATEADD(dd, 0, DATEDIFF(dd, 0, DTTM))
Order by DATEADD(dd, 0, DATEDIFF(dd, 0, DTTM)) desc

Newer versions of SQL Sever will support a Date type, so you can do this instead:

SELECT CAST(DTTM AS Date) AS DTTM,COUNT(*) AS Total
FROM 
[Audits].[dbo].[Miscount]
Group by CAST(DTTM AS Date)
Order by CAST(DTTM AS Date) desc

Upvotes: 5

Related Questions