Datetime selection query group by day.

Question

Hello, I've got a question for my SQL query I've got too write. It's a long time ago since I've written an query so I could use some help with mine. I've tried looking for examples but didn't find the right result. Ive written an query but its really isn't working for me.. What im trying to do is get the sum of the total power consumption for each date in my database. My table looks like:

|HistoryProbes|
|-------------|
|id (int) pk  |
|timestamp (datetime)  formatted as: "yyyy-MM-ddTHH:mm:ss"|
|PowerConsumption (int)|

I've found a sample that did quite work.. But it isnt the best solution for me.. it can be found at : http://cephas.net/blog/2005/12/06/sql-server-group-by-datetime/

So far i got this working

SELECT distinct CONVERT(varchar, timestamp, 111) AS thedate
FROM         HistoryProbes

I got values 25/11/2009 and 24/11/2009 but i cant manage to get the sum of the PowerConsumption

Thanks.

Answer 1

select CONVERT(varchar, timestamp, 111) as timestamp_by_day
, sum(PowerConsumption) as total_power
from HistoryProbes
group by CONVERT(varchar, timestamp, 111)
order by CONVERT(varchar, timestamp, 111)

Answer 2

Something like this will give you the sum per day

DECLARE @HistoryProbes TABLE(
     id INT,
     timesmp  DATETIME,
     PowerConsumption INT
)

INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 1, '01 Jan 2009 12:00:00',1
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 2, '01 Jan 2009 11:00:00',2
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 3, '01 Jan 2009 13:00:00',3
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 4, '01 Jan 2009 14:00:00',4
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 5, '02 Jan 2009 12:00:00',14
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 6, '02 Jan 2009 11:00:00',24
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 7, '03 Jan 2009 13:00:00',34
INSERT INTO @HistoryProbes (id,timesmp,PowerConsumption) SELECT 8, '03 Jan 2009 14:00:00',44

SELECT  DATEADD(dd,0, DATEDIFF(dd,0,timesmp)),
     SUM(PowerConsumption)
FROM    @HistoryProbes
GROUP BY DATEADD(dd,0, DATEDIFF(dd,0,timesmp))

Answer 3

Try this:
SELECT Convert(varchar, timestamp, 111) as thedate, SUM(PowerConsumption) as Total
FROM HistoryProbes
GROUP BY Convert(varchar, timestamp, 111)

I'm not sure why you need distinct in there; since you're not joining to any other tables