0

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'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1777929
  • 777
  • 1
  • 10
  • 28
  • 3
    Don't put `startdate` in your SELECT. Instead put `CAST(STartdate as Date) as startDate` in your select so it matches your group by. – JNevill May 11 '17 at 17:43
  • @jevill Not required in select i think – Kannan Kandasamy May 11 '17 at 17:47
  • Thank you JNevill and @KannanKandasamy. That worked. – user1777929 May 11 '17 at 17:52
  • Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Tab Alleman May 11 '17 at 18:22

1 Answers1

1

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)
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60