Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcoonrod
Starting Member
3 Posts |
 Posted - 2010-07-30 : 12:08:57
|
| I was wondering if someone could give me a little help with a query. We have a fishing league that the anglers register the fish they caught each week online at a website I created. I am looking to create a query that will give me the total fish caught by an angler each week.We have 16 weeks in the league. There are 30 anglers. Here is my table structure: Table name: Fish_Scores Id (PK, int, not null) Species (nvarchar(10), not null) FishLength (decimal(4,2), not null) Points (int, not null) AnglerId (int, not null) DateRegistered (datetime, not null) Table name: Anglers Id (PK, int, not null) fname (nvarchar(50), not null) lname (nvarchar(50), not null) username (nvarchar(100), not null) Thanks for any and all help, Marty |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-30 : 12:19:29
|
| There's no field to determine which week the fish were caught in |
 |
|
|
mcoonrod
Starting Member
3 Posts |
Posted - 2010-07-30 : 13:53:25
|
| Good point, I forgot to mention that our week starts at 5:00pm on Thursdays. Could the weeks not be taken from the DateRegistered field in the Fish_Scores table given a starting date of May 6, 2010? Thanks for the response |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-30 : 14:02:16
|
| do they register every week? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-07-30 : 14:02:48
|
| SELECT a.fname,a.lname, DATEDIFF(day,'20100506 17:00:00',dateadd(hour,5,getdate()))/7 ,count(*) as TotalFishCaughtFROM Anglers aINNER JOIN fish_scores f ON a.ID = f.AnglerIDGROUP BY a.fname,a.lname, DATEDIFF(day,'20100506 17:00:00',dateadd(hour,5,getdate()))/7JimEveryday I learn something that somebody else already knew |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-07-30 : 14:03:56
|
quote:
do they register every week?
They will now or my query will have gaps!JimEveryday I learn something that somebody else already knew |
|
|
|
mcoonrod
Starting Member
3 Posts |
Posted - 2010-07-30 : 14:20:10
|
| Yes, they register the fish they caught as long as they were successful and the fish meets a minumum lenght criteria.I tried jimf's query and I am getting an error:"Each GROUP BY expression must contain at least one column that is not an outer reference" |
|
|
|
|
|
|
|
|
Total fish by angler by week