Solved

# Select Sum by Day, Month or Year

Posted on 2007-10-12
Medium Priority
801 Views
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
Question by:sl1nger

LVL 143

Expert Comment

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

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

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

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

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
Course of the Month14 days, 4 hours left to enroll