Solved

SQL Aggregation Question

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

705 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

18 Experts available now in Live!

Get 1:1 Help Now