如何在SQL Server中按日期对数据进行分组？ [英] How to Group data only by date in SQL Server?

问题描述

您好。我在SQL Server Express 2005中进行分组时遇到问题



我有一个 DATETIME COLUMN 但我只想按日期分组。



这里我的SQL语句：

Hello. I'm having a problem with grouping in SQL Server Express 2005

I have a DATETIME COLUMN but i want to group it only by date.

Here my SQL Statement:

SELECT
  (u.FirstName + ' ' + u.LastName) AS [FullName]
 ,d.user_id
 ,CONVERT(varchar,d.log_date,101) AS log_date
 ,min(d.login_time) as LOG_IN
 ,max(d.logout_time) as LOG_OUT
 ,sum(d.totaltime) as TOTHrs
FROM
  tbldtr d
  INNER JOIN tblUsers u ON d.user_id = u.User_Id
WHERE
  d.user_id = 'ADMIN1'
  and d.log_date BETWEEN '6/1/2013' AND '6/15/2013'
GROUP BY
  DATEADD(day, DATEDIFF(day, 0, log_date), 0)
 ,u.FirstName
 ,u.LastName
 ,d.user_id
ORDER BY
  d.log_date asc

但是它给了我这个错误：

But it give me this error:

Column 'tbldtr.log_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

提前感谢您。 ！

Thank you in advance.!

推荐答案

你的错误显示组列表中的那些列应该在选择列表中

这里你必须添加CONVERT（varchar，d组列表中的.log_date，101）

your error shows that those columns in group list should be in select list
here u have to add CONVERT(varchar,d.log_date,101) in group list

这篇关于如何在SQL Server中按日期对数据进行分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！