CODEWITHSUNDEEP
sqlsql-server
greatWallace
greatWallace

Reputation: 37

SQL Group by date and sum another col (Microsoft SQL Server)

I have a datatable and I want to group by different days. But in this column I also have hours, minutes and seconds ... What I want to do exactly is group by date and sum another column.

For example:

Date                       DataToSum
2020-05-20 10:29:37.000        5
2020-05-20 11:18:12.010        3
2020-05-20 12:31:56.020        8
2020-05-21 10:19:36.000        4
2020-05-22 09:28:17.000        1

So the results have to be this:

2020-05-20 -> 16
2020-05-21 -> 4
2020-05-22 -> 1

How can I do this using SQL?

I am using Transact-SQL with Microsoft SQL Server Management Studio.

This is what I am trying:

Select distinct convert(varchar, col_Date, 111), Sum(cats) from table1
Group by col_Date
Order by convert(varchar, col_Date, 103) desc

Upvotes: 0

Views: 108

Answers (2)

Raymondo
Raymondo

Reputation: 587

The other option to maybe make it neater and I also find good practice on computed/concatenated columns is a Common Table Expression (CTE)

With ExampleCTE AS
(
Select  convert(date, col_Date) As dte
,       cats
From    table1
)
Select  dte
,       SUM(cats) As SumOfCats
From    ExampleCTE
Group By dte
Order By dte Desc;

Whilst your example is an easy enough one to follow, you may find in the future with more complex scenarios that a CTE makes things easier to read

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your code looks like SQL Server. In that database:

select convert(date, col_Date) as dte, Sum(cats)
from table1
group by convert(date, col_Date)
order by convert(date, col_Date) desc;

The key is that you need to repeat the expression in the group by.

Note: This converts the value to a date rather than to a string. I don't recommend mixing string and date functions, unless you want to convert the date/time to a string with a particular format.

Upvotes: 3

Related Questions