Solved

SQL Aggregation Question

Posted on 2007-04-09
5
189 Views
Last Modified: 2010-03-19
If I had a table with some data ordered by date.  Is there a way I can pull the data to aggregate the data based on the individual day?  So if I had a table with one column is Date, the other is Amount.  I want to return a selection that  has a row for each distinct date and the sum of all the amounts on that date.  Thanks
0
Comment
Question by:kayhustle
  • 3
5 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 18879979
SELECT Date, SUM(Amount) FROM Table GROUP BY Date
0
 
LVL 1

Author Comment

by:kayhustle
ID: 18880051
Ok, lets say the actual date field could be 11:30pm 1/1/07 or 12:30 1/1/07, but I want it to aggregate by day, even though the dates in the table are aggregated by hour?
0
 
LVL 27

Expert Comment

by:yodercm
ID: 18880199
Assuming you are using a datetime field, you can GROUP BY SUBSTRING(Date FROM 0 FOR 10)

This picks off the first 10 characters of the Date field, which should be 2007-04-10 format.
0
 
LVL 27

Accepted Solution

by:
yodercm earned 500 total points
ID: 18880211
There are also MySQL functions YEAR, MONTH, DAY, so you might try

GROUP BY YEAR(Date), MONTH(Date), DAY(Date).  

I'm not sure which would be more efficient, so if it matters, you might want to try both and put a timer on to see which way is faster.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18880484
as you posted in SQL Server 2005, I suggest to give you the mssql query:

select CONVERT(varchar(10), yourdatefield, 120) as day_value , sum(amount) sum_amount
FROM yourtable
group by CONVERT(varchar(10), yourdatefield, 120)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 94
sql server query 12 26
SQLCMD Syntax 2 17
How to use spatial data types in SQL Server Database project 2 24
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question