sql-server


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'
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)

Related Links

SQL Server BIT data type reports differently for View and Table query
FreeTDS hangs indefinitely after Microsoft SQL Express 2012 Install
Does ROW_NUMBER affect performance?
Sql server CASE expression columns indexing
Alternative to Microsoft ODBC Drivers for 64-bit Windows 7 machine (get Excel data to SQL)
Executing SQL query from VBA - run time error
Gap between initializing database and setup transactional replication?
SQL Server XML PATH removing XML
Cross database ownership chaining with Master Data Services DB
Probem accessing a SQL Server from a WIndows service running on a domain user
High performance optional where clause in SQL Server (without using dynamic query) [duplicate]
How to determine performance of the query in SQL Server
Does UPDATED() function in SQL trigger return true when rows are deleted?
Extract XML using OPENXML()
Query performance differs with just date change in where clause
How to simplify same table nested query

Categories

HOME
sas
crystal-reports
plsql
redis
sed
nuxeo
ocaml
backup
decorator
deep-linking
extract
histogrammar
java-home
avl-tree
codeblocks
dropbox
jboss-eap-7
tree-traversal
google-people
source-maps
remove-method
guile
vcard
xilinx-ise
weinre
sharp
red-black-tree
gpib
helix-3d-toolkit
geo
data-extraction
rhomobile
recurrence-relation
phpspreadsheet
axis-labels
elfinder
strstr
sql-like
active-model-serializers
flex4.5
stdclass
ssh.net
google-account
constants
android-maps-v2
chown
persistent
paas
date-range
polar-coordinates
spyne
rsqlite
uistackview
freetype2
database-backups
fps
inet
sun-codemodel
throughput
enyo
mysqldumpslow
python-winshell
prezto
apache-mina
scrollspy
ember-cli-addons
es2015
cpan
django-1.6
genetic-programming
funq
power-law
system.web
phpdocx
android-looper
opensocial
roxygen
arangodb-php
java.util.date
onselect
ruboto
fileconveyor
qt4.6
transitive-closure-table
linkedhashset
moq-3
static-variables
applicationcontext
supersized
nemerle
datamember
handwriting
gtk2hs
phpcrawl
pivotal-crm
squeel
querypath
microsoft.ink
lazy-c++
internals
interface-design

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App