General discussion

Locked

SQL Help

By aplatfl ·
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.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Help

by SteveD In reply to SQL Help

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

Collapse -

SQL Help

by aplatfl In reply to SQL Help

Poster rated this answer

Collapse -

SQL Help

by aplatfl In reply to SQL Help

This question was closed by the author

Back to Desktop Forum
3 total posts (Page 1 of 1)  

Related Discussions

Hardware Forums