CODEWITHSUNDEEP
sql-servergroup-bygrouping
user1777929
user1777929

Reputation: 797

SQL Server 2014 - How to group by date only from a datetime column

I have a table that lists data every 15 minutes. So my date column data is like '2017-04-18 00:15:00.000'

I'm trying to get sum of those columns for that entire day but my grouping doesn't want to work.

SELECT 
    Sum(Field 1),
    Sum(Field 2),
    Sum(Field 3)
FROM 
    MyTable
WHERE 
    StartDate >= '2017-04-18'
    AND StartDate <= '2017-04-19'
GROUP BY 
    CAST(StartDate AS DATE)
    --GROUP BY CONVERT(CHAR(10),StartDate,103)
    --GROUP BY StartDate

The GROUP BY StartDate gives me records like so:

2017-04-18 08:00:00.000 13  3   22  0
2017-04-18 08:15:00.000 15  1   16  0
2017-04-18 08:30:00.000 14  7   13  0
2017-04-18 08:45:00.000 16  3   18  0
2017-04-18 09:00:00.000 17  4   21  0

I've tried:

GROUP BY CAST(StartDate AS DATE)

as well as

GROUP BY CONVERT(CHAR(10), StartDate, 103)

These above two group by clauses give me the same error message:

The column 'StartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How would I GROUP by Date so that I can GROUP the data by date as '2017-04-18'

Upvotes: 0

Views: 2461

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

You can try a query like below

SELECT 
      CAST(StartDate AS DATE) as S
      ,Sum(Field 1)
      ,Sum(Field 2)
      ,Sum(Field 3)

FROM MyTable

WHERE StartDate >= '2017-04-18'
  AND StartDate <= '2017-04-19'

GROUP BY CAST(StartDate AS DATE)

Upvotes: 1

Related Questions