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 multiple join reference table from different tables
How to update a list of different data to a table in sql server?
sql server decimal data type issue
SQL Server get closest timestamp (datetime) to hh:mm:ss (varchar) per row per day
SQL Server 2016 Mobile Report Datasets Expire about 30 Days
How can I remove older SQL Server instances?
How can I install MONDIAL on top of SQL Server?
I cannot insert or update after I have created trigger on a SQL Server table
Change data length using SSIS DerivedColumn
Spit graphical query plans as jpegs?
Calculate Nearest Neighbor in SQL Server
How to make dynamic scale for bar chart when chart inside a cell. SSRS 2012
Update table column for removing unwanted double quotes in SQL Server
Which DB driver to use DbVisualiser with Azure SQL?
How do you pass the property of an MVC controller into the where clause of a string literal query? [duplicate]
How to sum if within percentile in SQL Server?

Categories

HOME
sharepoint
google-apps-script
security
ssas-2012
freepascal
firebase-app-indexing
ocaml
google-contacts
extract
orientation
recyclerview
vsftpd
i2c
currency
metronic
flexbox
cortex-a
dryioc
dlib
store
equalizer
lenskit
netezza
python-unicode
movies
clover
rails-activerecord
remove-method
tflearn
visual-c++-2017
ui5
interrupt-handling
edge-detection
partial-application
google-api-dotnet-client
strophe
spring-profiles
m2e
oscommerce
liquid-xml
es-shell
strstr
silverlight-3.0
wcf-security
constants
gauss
web-deployment-project
jgraph
shutdown
rpostgresql
createobject
sqldatareader
barcode-printing
android-navigationview
xib
vst
radians
zurb-foundation-apps
asp.net-mvc-partialview
usb-drive
twgl.js
mfc-feature-pack
loopj
ninject.web.mvc
sem
urn
castle-windsor-3
leadtools-sdk
xaml-designer
magento-1.12
jcr-sql2
python-ggplot
grunt-express
vcl
power-law
code-testing
ftp4j
visual-studio-2003
zend-lucene
icsharpcode
ubuntu-11.10
vim-powerline
blockingqueue
die
newsstand-kit
adsl
noir
email-spec
loadui
osx-leopard
grooveshark
front-controller
asp.net-mvc-controller
method-signature
weborb





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