日期时间选择查询分组按天

Question

我有一个问题，我的SQL查询我写太写了。很久以前，我写了一个查询，所以我可以用我的一些帮助。我试过寻找例子，但没有找到正确的结果。我写了一个查询，但它真的没有为我工作.. 即时消息试图做的是得到我的数据库中的每个日期的总功耗的总和。 我的表如下所示：

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

我发现一个样品是没有相当的工作。但它不是最好的解决方案我.. 其可以在发现：http://cephas.net/blog/2005/12/06/sql-server-group-by-datetime/

到目前为止，我得到这个工作

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

我值25/11/2009和24/11/2009，但我不能设法得到的功率消耗的总和

谢谢。

Answer 1

像这样的东西会给你的总和，每天

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 2

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 3

试试这个：
SELECT Convert(varchar, timestamp, 111) as thedate, SUM(PowerConsumption) as Total
FROM HistoryProbes
GROUP BY Convert(varchar, timestamp, 111)

我不知道为什么你需要在那里不同的;因为你没有加入任何其他表格