?
Solved

Select Sum by Day, Month or Year

Posted on 2007-10-12
4
Medium Priority
?
801 Views
Last Modified: 2008-01-09
I'm working on a chart and would like to select the sum of orders on any certain day, month or by a year.

select sum(orders), date_entered from orders where date_entered = getDate() - 365
group by date_entered
0
Comment
Question by:sl1nger
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20069199
can you explain a bit in detail what exactly you have the problem with?
1 important detail: what data type is the field date_entered, and is it containing a time portion?
0
 
LVL 6

Accepted Solution

by:
twintai earned 1200 total points
ID: 20069735
i think what you need is data part,

select datepart(year,date_entered) as Year, sum(orders)
from orders
where date_entered = getDate() - 365
group by datepart(year,date_entered)
order by Year asc

assuming orders column as numbers. If you want record counts, then you should use count(orders) instead. with regards to the other..
datepart(year,date_entered)  gives total by month
datepart(week,date_entered) gives total by week
datepart(weekday,date_entered)  gives total by weekday

your original query will give total for each day there is a record
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 20081081
This will give you daily totals for the past year (365 days):

SELECT SUM(orders), date_entered
FROM orders
WHERE date_entered BETWEEN DATEADD(DAY, -365, CONVERT(CHAR(8), GETDATE(), 112) ) AND GETDATE()
GROUP BY date_entered  --WITH ROLLUP  --add this clause to get a yearly total at the end
ORDER BY date_entered


For calendar month totals, you can do this:
SELECT SUM(orders), CONVERT(CHAR(7), date_entered, 120)  AS [YYYY-MM]
FROM orders
WHERE date_entered BETWEEN DATEADD(DAY, -365, CONVERT(CHAR(8), GETDATE(), 112) ) AND GETDATE()
GROUP BY CONVERT(CHAR(7), date_entered, 120)  --WITH ROLLUP  --add this clause to get a yearly total at the end
ORDER BY CONVERT(CHAR(7), date_entered, 120)
0
 

Author Comment

by:sl1nger
ID: 20088371
twintai --  I liked yours the best but found these options where more appropriate for sql server

http://support.microsoft.com/kb/186265

   Ms    for Milliseconds
   Yy    for Year
   Qq    for Quarter of the Year
   Mm    for Month
   Dy    for the Day of the Year
   Dd    for Day of the Month
   Wk    for Week
   Dw    for the Day of the Week
   Hh    for Hour
   Mi    for Minute
   Ss    for Second
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…
Suggested Courses

809 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