Discussion Locked

SQL Help

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table storing tardy data. The fields are userid, date, tardymins
and Supid. How can i write a query to sum the tardymins for each userid that
have the same supid within a date range? My code below doesn`t seem work.

Select Userid, sum(Tardymins) as [Totaltardys], Supid, Date
From Tardys
Where Supid Like 'a2a'
And Date Between #1/01/01# And #1/31/01# .

Thanks in advance.

3 replies

by steved

Try this:

Select Userid, sum(Tardymins) as [Totaltardys]
From Tardys
Where Supid Like 'a2a'
And Date Between #1/01/01# And #1/31/01#
Group By Userid
Order By Userid

In general, to use an aggregate function such as Sum you have to have aGroup By clause and you can't specify any columns in the Select list that aren't in the Group By clause (except those used in aggregate functions). A moment's thought will reveal why.

This query will give you the sum for each Supid and each associated Userid of tardymins during that date range:

Select Supid, Userid, sum(Tardymins) as [Totaltardys]
From Tardys
Where Date Between #1/01/01# And #1/31/01#
Group By Supid, Userid
Order By Supid, Userid

Steve Diamond

This discussion is closed to new replies.