Solved

SQL Aggregation Question

Posted on 2007-04-09
5
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
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:Cornelia Yoder
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:
Cornelia Yoder 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

Percona Monitoring and Management and Grafana

Proactive monitoring is vital to a highly-available environment. We have a quick start guide on Experts Exchange for Grafana users.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 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