Using Sum in SQL

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

William
Tue Sep 02 09:27:41 CDT 2003

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).....
"Grant" <Grant@nutrikids.com> wrote in message
news:%23TdSOmVcDHA.1580@tk2msftngp13.phx.gbl...
> 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
>
>

Richard
Tue Sep 02 10:21:26 CDT 2003

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)


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:uX1AP4VcDHA.2572@TK2MSFTNGP12.phx.gbl...
> 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).....
> "Grant" <Grant@nutrikids.com> wrote in message
> news:%23TdSOmVcDHA.1580@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

William
Tue Sep 02 11:44:57 CDT 2003

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.
"Richard Brown" <rbrown@easylift.org> wrote in message
news:eKb8ZXWcDHA.2632@TK2MSFTNGP12.phx.gbl...
> 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)
>
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:uX1AP4VcDHA.2572@TK2MSFTNGP12.phx.gbl...
> > 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).....
> > "Grant" <Grant@nutrikids.com> wrote in message
> > news:%23TdSOmVcDHA.1580@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>