Group by date not working in sql server

Question

I have this query

SELECT TOP 1000 [lid]
      ,[userid]
      ,[datetime]
      ,[batch_id]
      ,[actionid]
      ,[pages]
      ,[group]
      ,[sitecode]
  FROM [mydb].[dbo].[tbl_recon_scan]
  GROUP BY CAST([datetime] AS DATE)

it seems not working with group by. I have looked at this and this guides, I could not find solution.

Anyone can tell me my problem with the query?

do you want this SELECT DISTINCT TOP 1000 [lid] ,[userid] ,CAST([datetime] AS DATE) ,[batch_id] ,[actionid] ,[pages] ,[group] ,[sitecode] FROM [mydb].[dbo].[tbl_recon_scan] — Neeraj Prasad Sharma, Aug 24 '15 at 06:23
You can't use columns in the select clause if they are not in the group by clause or an aggregating function (`sum`, `max` etc'). I know MySql allows it, but sql server does not. — Zohar Peled, Aug 24 '15 at 06:26
You query not Contain Aggregate function then you can't use GROUP BY clause — Chanom First, Aug 24 '15 at 06:28
What output are you expecting? Why are you grouping by one column and displaying others? — Po-ta-toe, Aug 24 '15 at 06:49

score 0 · Answer 1 · edited Aug 24 '15 at 11:56

0

You don't use any aggregate functions in query. So it shows every individual record.

Visit these link:

edited Aug 24 '15 at 11:56

Mark Rotteveel

100,966
191
140
197

answered Aug 24 '15 at 06:27

Manojkumar

91
11

Group by date not working in sql server

1 Answers1