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

Run SSIS/SSDT on app server
SSIS - How to use variable as a source to compare with data in tables in fuzzy lookup?
How do I get the key of previous ordered rows with an unknown offset?
xp_cmdshell - The system cannot find the path specified
how to see or create the diagram from an existing SSAS cube?
How to Check Valid Date Time from Sql Table
SQL server case statement
SQL Update NULL value from select statement query
How to export hyperlinks in excel 2013 from database
ASP.NET Core RC2 Seed Data using Services
Parsing a SQL table to show parts of one column in two different columns
Power Pivot Memory Error when importing tables with a timestamp (rowversion) column
Case When Statement First Character Match Letter Range
Trying to Load Data from Excel into SQL Server. 32-bit Excel and 64-bit SQL Server Throws Error
Error executing Select Into Statement
Convert SQL Server table data from XML to JSON

Categories

HOME
cntk
pug
shopify
jar
winapi
bing
windows-7
bower
recyclerview
specflow
ios10
adsense
filter
user
google-spreadsheet-api
google-plus
typeahead
ejs
spring-cloud-config
uisplitviewcontroller
cross-platform
bootstrap-popover
apache-karaf
atlassian-plugin-sdk
jpeg2000
ms-access-2007
eclipselink
zoho
plsqldeveloper
sensu
piwik
metis
hash-collision
maximo
cgal
google-api-dotnet-client
mangodb
datadog
vegan
strophe
receipt
qtablewidget
nsurlconnection
service-locator
nunit-3.0
rider
inkscape
alchemy.js
excel-interop
upsert
defold
spell-checking
shutdown
component-pascal
windows-95
scd
photography
react-native-fbsdk
fontconfig
unity3d-editor
gulp-typescript
build-process
glkit
spring-lemon
emokit
gmsmapview
yoothemes
fortran90
acm
file-diffs
datasnap
google-hadoop
hana-xs
sysctl
named-ranges
odftoolkit
ogr2ogr
genetic-programming
java-collections-api
power-law
adaptive-compression
aquafold
proxies
argument-passing
codeigniter-a3m
visual-c++-2010-express
circos
document-database
confusion-matrix
onselect
vmware-server
android-4.0
sequelpro
ubuntu-11.10
enumerators
selectonemenu
adsl
noir
ruby-debug
post-redirect-get
loadui
winverifytrust
front-controller
openvg
soft-keyboard
msf

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