Solved

SQL Aggregation Question

Posted on 2007-04-09
5
188 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 142

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now