CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
user1745767
user1745767

Reputation: 1071

Select data by date and sum data - SQL Server 2005

I'm coming from MySQL and trying to code T-SQL on SQL Server 2005, and I'm finding it completely different.

Here's what I want to do (using MySQL)

select sum(datapoint) as sum, date(mytimestamp) as date
from datalog
where datapoint = '27'
group by date

i.e. get a list of data summed and grouped by date.

Upvotes: 3

Views: 867

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44356

converting to varchar is not a good solution, simply remove the timepart from your timestamp and group by it like this:

select sum(datapoint) as sum, dateadd(d, 0, datediff(d, 0, mytimestamp)) as date
from datalog
where datapoint = '27'
group by datediff(d, 0, mytimestamp)

Upvotes: 0

John Woo
John Woo

Reputation: 263933

Answer when sql server 2008 was tagged

select sum(datapoint) as [sum], CAST(mytimestamp AS DATE) as [date]
from datalog
where datapoint = '27'
group by CAST(mytimestamp AS DATE)

For SQL Server 2005

SELECT   CONVERT(VARCHAR(11), timestamp, 111) DATE, 
         SUM(datapoint) totalDatapoint
FROM     table1
GROUP BY CONVERT(VARCHAR(11), timestamp, 111)
ORDER BY DATE ASC

SQLFiddle Demo

Upvotes: 1

Related Questions