SQL help: SUM & GROUP BY functions

[cow]

i have a page that displays the last 5, 10 or 20 days of sales. the statement looks like this:

SELECT TOP "+date_range+" SalesDate, SUM(DailySales) AS total_sales
FROM Sales
GROUP BY SalesDate
ORDER BY SalesDate DESC

the resulting table looks like this when date_range = 5:

SalesDate	total_sales
06/25/2003	17
06/24/2003	25
06/23/2003	35
06/20/2003	11
06/19/2003	22

the problem is that i also want to display the sum of each day's total_sales. any ideas??? i've tried including a subquery, but that doesn't allow me to sum up group calculations...tia!

[DaveMaxwell]

Just do a select sum(DailySales) where daterange < CalculatedDate

You can calculate the date difference in the SQL statement, but I'm having a brainfade right now, so I'll look at it again tomorrow.....bedtime...

[cow]

the only problem is that my dates are strings, not ints. so i can't calculate the dates.

[vgarcia]

the only problem is that my dates are strings, not ints. so i can't calculate the dates.

Use cast(columnname as type) if you want to change the type of a column on the fly. Let's say that some newbie DBA set up your dailysales field as a varchar() instead of a currency or numeric data type. You can change it like so:

SELECT SUM(CAST(DailySales AS money)) 
FROM tablename;