Using Sum in SQL

  • Thread starter Thread starter Grant
  • Start date Start date
G

Grant

I am trying to figure out how to get the sum for each day.



This is what I have for SQL



SELECT fldDate, sum(total) FROM tbl GROUP BY fldDate ORDER BY fldDate



The date field has date and time in it, I assume if I can format the date
field to use only the date, then query will work. Please let me know how to
format the date in query or there is a better way.



Thanks
 
CAST(ROUND(CAST(total AS FLOAT),0,1) AS DATETIME)
or CAST(ROUND(CAST(total AS FLOAT),0,1) AS SMALLDATETIME)

You may also want to consider making a UserDefined Function out of it if you
are in SQL SErver or Oracle like this...\


CREATE FUNCTION DateOnly_dt
(@DateAndTime AS datetime)
RETURNS datetime
AS

BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS datetime)
END

the you could just use SELECT dbo.DateOnly_dt(total).....
 
William,

Not sure how you were trying to answer his question, but he was asking how
to get the group by to function properly with just the date only... which
would be this way.... (note, you cant use CAST since it doesnt give you
formatting options)

select convert(datetime, fldDate, 110) as sumDate, sum(total) as sumtotal
from tbl group by convert(datetime, db.date, 110) order by convert(datetime,
fldDate, 110)
 
If you are trying to determine a date part of a DateTime Field, and in this
regard group by that date....I think either approach will work. You're
right about the formatting...when I originally read the question it seemed
that gettting the aggregate to work was the reason he was concerned with
formatting.

Both methods yield the same results numerically the only difference being
the formatting. but you make a good point ..thanks for the post.
 
Back
Top