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

How can I find out which rows (if any) have a non-unique value in a column?
Can't resolve references to Microsoft.SqlServer.DTSPipelineWrap in BIDS 2008 Script Editor
SQL Server: how to create a stored procedure
Difficulty Connecting To SQL Server Express 2012 from VB.NET (Express)
Using transaction with SQL Server Linked Server - Transaction context in use by another session
Specify database when creating schema
After Insert Trigger not Working
Force unique varchar(255) column value in a SQL Server table with Entity Framework
Error converting varchar to numeric in SQL subtract query
Convert datetime to yyyy-mm-dd:hh in SQL Server
MS SQL Index column sort order matters?
Entity Framework 6 - Many-to-Many Table with Additional Information
Conversion of reports from ssrs 2008 to ssrs 2012
Restore same database multiple times [closed]
how to change default language of SQL server management studio?
How to insert records from one table to other when multirow inserted in table using trigger?

Categories

HOME
vue.js
oauth
nuxeo
json-ld
google-sheets-api
ubuntu-12.04
couchdb-2.0
triggers
xcode8.3
aruco
flexbox
cakephp-2.9
android-source
intellij-plugin
x-frame-options
jacoco
cloudflare
lstm
factor-analysis
movies
internet-explorer-8
plupload
backpropagation
go-cd
cronet
activepython
switching
xilinx-ise
weinre
autofill
postgresql-9.2
exponential
openpgp
remote-server
liquid-xml
elfinder
code-behind
polyfills
exiftool
jrules
z3py
number-theory
livefyre
persistent
pagefile
butterknife
fontconfig
hpcc
infinite-scroll
sqldatareader
verbose
system.web.ui.webcontrols
gradle-script-kotlin
push-diffusion
aescryptoserviceprovider
brython
squirrel
codesign
remoteapp
unity5.3
android-snackbar
windowlistener
mogrify
vine
pyopengl
cpu-cores
pylearn
shift-jis
ultrawingrid
em
angulartics
goinstant
va-list
html-form-post
carddav
backbone-relational
mstsc
shortcuts
htmltextwriter
fieldset
android-contextmenu
domain-calculus
sequelpro
socketstream
autostart
cosm
redirectstandardoutput
userid
concurrent-programming
separation-of-concerns
entity-framework-ctp5
handwriting
audiostreamer
email-spec
drawtext
exitstatus
code-design
asp.net-mvc-controller
longjmp
nt4
evb
project-lifecycle
cots

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