CODEWITHSUNDEEP
sql-serversum
iAm.Hassan
iAm.Hassan

Reputation: 53

SQL Sum for every day

I have lots of data in my count field with different dates. How can I make a query that groups by date and sum of count for every date ?

My sample data is below :

  count    date
   2       2018-07-03 19:06:24.501
   5       2018-07-03 02:06:24.112
   3       2018-07-04 17:06:24.426
   2       2018-07-04 14:06:24.241
   5       2018-07-05 19:06:24.431
   2       2018-07-05 11:06:24.521
   5       2018-07-06 22:06:24.103
   4       2018-07-06 08:06:24.530

Now I want my result data :

 sum count    date
   7        2018-07-03 
   5        2018-07-04 
   7        2018-07-05 
   9        2018-07-06

Upvotes: 1

Views: 1737

Answers (3)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

CAST your datetime to date

SELECT SUM(a.count),CAST(a.date as date)
FROM <table> a
GROUP BY CAST(a.date as date)

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use ANSI SQL cast() function with GROUP BY Clause :

select sum(cnt), cast(dt as date)
from table t
group by cast(dt as date);

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

You can cast your Date column to Date only and get rid of the time part of the datetime values, also group by that Date and it should give you what you want. Something like this...

SELECT  COUNT(*)                AS [Sum Count]
      , CAST([Date] AS DATE)    AS [Date]
FROM TableName
GROUP BY CAST([Date] AS DATE)

Upvotes: 1

Related Questions