Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have three tables: tbl_Player, tbl_MatchDetails, tbl_MatchStat with some data:

tbl_Player

CREATE TABLE [dbo].[tbl_Player](
    [PlayerID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Player] PRIMARY KEY CLUSTERED 
(
    [PlayerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_Player] ON
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (1, N'L Messi')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (2, N'C Ronaldo')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (3, N'Neymar')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (4, N'Rooney')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (5, N'K Aguero')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (6, N'Gomez')
SET IDENTITY_INSERT [dbo].[tbl_Player] OFF

tbl_MatchDetails

CREATE TABLE [dbo].[tbl_MatchDetails](
    [MatchID] [int] IDENTITY(1,1) NOT NULL,
    [Tournament] [varchar](50) NULL,
    [TeamA] [varchar](50) NULL,
    [TeanB] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_MatchDetails] PRIMARY KEY CLUSTERED 
(
    [MatchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_MatchDetails] ON
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (1, N'CL', N'Real', N'Barca')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (2, N'CL', N'Barca', N'United')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (3, N'League', N'Barca', N'Real')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (4, N'CL', N'M City', N'Bayern')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (5, N'League', N'Barca', N'Valencia')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (6, N'League', N'Real', N'Betis')
SET IDENTITY_INSERT [dbo].[tbl_MatchDetails] OFF

tbl_MatchStat

CREATE TABLE [dbo].[tbl_MatchStat](
    [StatID] [int] IDENTITY(1,1) NOT NULL,
    [MatchID] [int] NULL,
    [PlayerID] [int] NULL,
    [Goals] [int] NULL,
    [Minutes] [int] NULL,
 CONSTRAINT [PK_tbl_MatchStat] PRIMARY KEY CLUSTERED 
(
    [StatID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_MatchStat] ON
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (1, 1, 1, 3, 92)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (2, 1, 2, 1, 92)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (3, 2, 1, 2, 85)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (4, 2, 4, 2, 93)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (5, 3, 1, 1, 35)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (6, 3, 2, 0, 90)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (7, 4, 6, 2, 93)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (8, 4, 5, 0, 15)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (9, 5, 3, 2, 25)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (10, 5, 1, 4, 91)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (11, 6, 2, 3, 88)
SET IDENTITY_INSERT [dbo].[tbl_MatchStat] OFF

From this data i wanted to have a select statement which gives a summary of each player by tournament. For eg, from this data selecting by tournament 'CL' the query result may looks like

Name       MatchPlayed TotalGoals HighestScore Minutes Hattrick
L Messi              2          5            3     117        1
C Ronaldo            1          1            1      92        0
Rooney               1          2            2      93        0         
K Aguero             1          0            0      15        0
Gomez                1          2            2      93        0
share|improve this question

1 Answer

up vote 1 down vote accepted

Try

SELECT p.Name, 
       COUNT(m.MatchID) MatchPlayed, 
       SUM(s.Goals) TotalGoals,
       MAX(s.Goals) HighestScore,
       SUM(s.Minutes) Minutes,
       SUM(CASE WHEN s.Goals >= 3 THEN 1 ELSE 0 END) Hattrick
  FROM tbl_MatchDetails m JOIN tbl_MatchStat s
    ON m.MatchID = s.MatchID JOIN tbl_Player p
    ON s.PlayerID = p.PlayerID
 WHERE m.Tournament = 'CL'
 GROUP BY p.Name

Output:

|      NAME | MATCHPLAYED | TOTALGOALS | HIGHESTSCORE | MINUTES | HATTRICK |
----------------------------------------------------------------------------
| C Ronaldo |           1 |          1 |            1 |      92 |        0 |
|     Gomez |           1 |          2 |            2 |      93 |        0 |
|  K Aguero |           1 |          0 |            0 |      15 |        0 |
|   L Messi |           2 |          5 |            3 |     177 |        1 |
|    Rooney |           1 |          2 |            2 |      93 |        0 |

Here is SQLFiddle demo

share|improve this answer
Thank you very much, i was totally confused with joins for 3 tables. i got it. Thanks again. – barcanoj 20 hours ago
@barcanoj I'm glad it helped :) – peterm 20 hours ago
Sorry for adding queries but i was wondering how to calculate if player have played full match or not, logic is if he had played 90 or more minutes than that should be counted. i tried like COUNT(CASE WHEN s.Minutes>= 90 THEN 1 ELSE 0 END) FullMatch – barcanoj 20 hours ago
I think i have done with it i should use SUM instead of COUNT, am i right. Thanks again – barcanoj 19 hours ago
@barcanoj Yes you need to use SUM() for that – peterm 19 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.