June 23, 2008 at 9:49 pm
I have three tables as follows. I would like to create a query that will display a Player, Total number of events a player has played and the total wins(1) he has finished. I have tried the follow query, but can't get the finish to count the number of (1's) a player has finished.
Any help would be greatly appreciated.
SELECT Player.PlayerFirst AS Player, COUNT(Event.EventDate) AS Events, ScoreCard.Finished
FROM Player INNER JOIN
ScoreCard ON Player.PlayerID = ScoreCard.PlayerID INNER JOIN
Event ON ScoreCard.EventID = Event.EventID
WHERE (ScoreCard.Finished = 1)
GROUP BY Player.PlayerFirst, ScoreCard.Finished
CREATE TABLE [dbo].[Player](
[PlayerID] [int] IDENTITY(101,1) NOT NULL,
[PlayerFirst] [varchar](20) NULL,
CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventDate] [smalldatetime] NOT NULL,
[CourseID] [int] NOT NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[ScoreCard](
[ScoreCardID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NOT NULL,
[PlayerID] [int] NOT NULL,
[Finished] [smallint] NULL,
[Group] [varchar](1) NULL,
CONSTRAINT [PK_ScoreCard] PRIMARY KEY CLUSTERED
June 23, 2008 at 10:10 pm
You are almost there. Just need some sample data (in the form of insert statements so we can cut, paste, and execute), and what the expected output would be based on the sample data.
😎
June 24, 2008 at 9:36 am
Hope this helps.
INSERT INTO Player (PlayerFirst) Values ('Player1')
INSERT INTO Player (PlayerFirst) Values ('Player2')
INSERT INTO Player (PlayerFirst) Values ('Player3')
INSERT INTO Player (PlayerFirst) Values ('Player4')
INSERT INTO Event (EventDate, CourseID) Values ('01/01/2008', '1')
INSERT INTO Event (EventDate, CourseID) Values ('02/02/2008', '3')
INSERT INTO Event (EventDate, CourseID) Values ('03/03/2008', '5')
INSERT INTO Event (EventDate, CourseID) Values ('04/04/2008', '1')
INSERT INTO Event (EventDate, CourseID) Values ('05/05/2008', '2')
INSERT INTO Event (EventDate, CourseID) Values ('06/06/2008', '4')
INSERT INTO Event (EventDate, CourseID) Values ('07/07/2008', '2')
INSERT INTO Event (EventDate, CourseID) Values ('08/08/2008', '5')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('1', '104', '1', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('1', '101', '2', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('1', '102', '3', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('1', '103', '4', 'B')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('2', '102', '1', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('2', '103', '2', 'B')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('2', '104', '3', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('2', '101', '4', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('3', '104', '1', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('3', '102', '2', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('3', '103', '3', 'B')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('3', '101', '4', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('4', '101', '1', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('4', '103', '2', 'B')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('4', '104', '3', 'A')
INSERT INTO ScoreCard (EventID, PlayerID, Finished, [Group]) Values ('4', '102', '4', 'A')
June 24, 2008 at 9:54 am
Based on the sample data, what is the expected output? Need something to check our work against.
😎
June 24, 2008 at 10:04 am
Player / Events / Wins
Player1 / 4 / 1
Player2 / 4 / 1
Player3 / 4 / 0
Player4 / 4 / 2
I need to know the number of events a player has played and how many times a player has finished #1. There are over 200 events so I hope this sample helps.
June 24, 2008 at 10:21 am
How does this look:
with PlayerWins (
PlayerID,
Wins
) as (
select
Player.PlayerID,
sum(isnull(ScoreCard.Finished,0)) as Wins
from
dbo.Player
left outer join dbo.ScoreCard
on (Player.PlayerID = ScoreCard.PlayerID
and ScoreCard.Finished = 1)
group by
Player.PlayerID
)
SELECT
p.PlayerFirst AS Player,
COUNT(e.EventDate) AS Events,
pw.Wins
from
dbo.Player p
INNER JOIN dbo.ScoreCard s
ON p.PlayerID = s.PlayerID
INNER JOIN dbo.Event e
ON s.EventID = e.EventID
inner join PlayerWins pw
on (p.PlayerID = pw.PlayerID)
group by
p.PlayerFirst,
pw.Wins
order by
p.PlayerFirst;
😎
June 24, 2008 at 10:29 am
WOW. That worked out perfect. You're awesome. Thank you so much.
June 24, 2008 at 10:36 am
Here is a second option:
with PlayerWins (
PlayerID,
Wins
) as (
select
Player.PlayerID,
sum(isnull(ScoreCard.Finished,0)) as Wins
from
dbo.Player
left outer join dbo.ScoreCard
on (Player.PlayerID = ScoreCard.PlayerID
and ScoreCard.Finished = 1)
group by
Player.PlayerID
)
SELECT
p.PlayerFirst AS Player,
COUNT(s.EventID) AS Events,
pw.Wins
from
dbo.Player p
INNER JOIN dbo.ScoreCard s
ON p.PlayerID = s.PlayerID
inner join PlayerWins pw
on (p.PlayerID = pw.PlayerID)
group by
p.PlayerFirst,
pw.Wins
order by
p.PlayerFirst
😎
June 24, 2008 at 10:49 am
Same results. Option 1 and 2 works great.
June 24, 2008 at 11:07 am
Option 2 takes out one of the tables (Event). Not sure what the difference in IO's, execution plans, overall performance on a full production system.
😎
June 24, 2008 at 11:12 am
Lynn,
Is there a way to add an auto numeric column on the left of the results?
Example:
1
2
3
4 and so on
June 24, 2008 at 11:25 am
Not real sure what you are asking for here. Can you provide a more detailed example of what you are looking for?
😎
June 24, 2008 at 11:33 am
Add an auto numeric colum from the query statement you provided.
Example;
No. / Player / Events / Wins
1 / Player1 / 4 / 1
2 / Player2 / 4 / 1
3 / Player3 / 4 / 0
4 / Player4 / 4 / 2
5
6
7
June 24, 2008 at 11:49 am
Hows this:
with PlayerWins (
PlayerID,
Wins
) as (
select
Player.PlayerID,
sum(isnull(ScoreCard.Finished,0)) as Wins
from
dbo.Player
left outer join dbo.ScoreCard
on (Player.PlayerID = ScoreCard.PlayerID
and ScoreCard.Finished = 1)
group by
Player.PlayerID
), PlayerStats (
PlayerID,
Player,
Events,
Wins
) as (
SELECT
p.PlayerID,
p.PlayerFirst AS Player,
COUNT(s.EventID) AS Events,
pw.Wins
from
dbo.Player p
INNER JOIN dbo.ScoreCard s
ON p.PlayerID = s.PlayerID
inner join PlayerWins pw
on (p.PlayerID = pw.PlayerID)
group by
p.PlayerID,
p.PlayerFirst,
pw.Wins
)
select
row_number() over (order by PlayerID) as RowNumber,
Player,
Events,
Wins
from
PlayerStats
😎
June 24, 2008 at 12:02 pm
Perfect. It generated a RowNumber column. Thanks again.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply