Prev: SSRS Report - when rows expanded they jump under fixed column head
Next: Count over group
From: Marty on 30 Jul 2010 11:48
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
From: Eric Isaacs on 30 Jul 2010 14:37
Do you want a SUM of the fish scores or a COUNT of the fish scores?

Here's a quick answer, but you'll probably need to join it to the
anglers table to pull what you need from there, and add any of those
fields to the GROUP BY clause.


DECLARE @StartDate DATETIME
SET @StartDate = '20100601'

SELECT
DATEDIFF(DD, @StartDate, DateRegistered) / 7 + 1 AS Week,
AnglerId,
SUM(Points) AS TotalPoints,
COUNT(Id) AS TotalFish
FROM
Fish_Scores
GROUP BY
DATEDIFF(DD, @StartDate, DateRegistered) / 7 + 1,
AnglerId

-Eric Isaacs

From: Marty on 2 Aug 2010 11:58
Thanks for the reply Eric,
It does what I want, sort of.
I get the headers Week, AnglerId. TotalPoints, TotalFish
When I would like AnglerId, Week1, Week2, Week3, Week4, ....
With the TotalPoints as the value in the columns for the weeks. So it
would look like:

AnglerId Week1 Week2 Week3
1 4 2 3
2 1 5 2

Thanks again,

Marty


On Jul 30, 1:37 pm, Eric Isaacs <eisa...(a)gmail.com> wrote:
> Do you want a SUM of the fish scores or a COUNT of the fish scores?
>
> Here's a quick answer, but you'll probably need to join it to the
> anglers table to pull what you need from there, and add any of those
> fields to the GROUP BY clause.
>
> DECLARE @StartDate DATETIME
> SET @StartDate = '20100601'
>
> SELECT
>     DATEDIFF(DD, @StartDate, DateRegistered) / 7 + 1 AS Week,
>     AnglerId,
>     SUM(Points) AS TotalPoints,
>     COUNT(Id) AS TotalFish
> FROM
>     Fish_Scores
> GROUP BY
>     DATEDIFF(DD, @StartDate, DateRegistered) / 7 + 1,
>     AnglerId
>
> -Eric Isaacs

From: Erland Sommarskog on 2 Aug 2010 15:38
Marty (mcoonrod(a)gmail.com) writes:
> Thanks for the reply Eric,
> It does what I want, sort of.
> I get the headers Week, AnglerId. TotalPoints, TotalFish
> When I would like AnglerId, Week1, Week2, Week3, Week4, ....
> With the TotalPoints as the value in the columns for the weeks. So it
> would look like:
>
> AnglerId Week1 Week2 Week3
> 1 4 2 3
> 2 1 5 2

Working from Eric's query, we get:

WITH weeks AS (
SELECT DATEDIFF(DD, @StartDate, DateRegistered) / 7 + 1 AS Week,
� � AnglerId, Points
FROM Fish_Scores
)
SELECT AnglerID,
Week1 = SUM(CASE Week WHEN 1 THEN Points ELSE 0 END),
Week2 = SUM(CASE Week WHEN 2 THEN Points ELSE 0 END),
...
Week16 = SUM(CASE Week WHEN 16 THEN Points ELSE 0 END)
FROM weeks
GROUP BY weeks

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Eric Isaacs on 2 Aug 2010 16:46
> FROM   weeks
> GROUP  BY weeks

I would expect you would want to GROUP BY AnglerId instead of Weeks.

-Eric Isaacs
 |  Next  |  Last
Pages: 1 2
Prev: SSRS Report - when rows expanded they jump under fixed column head
Next: Count over group