Making An Leauge Series Tabel.

Hi
Running on SQL Server2005
I have an tabel Match, TeamInGroup, Team, Group and Series.

Want following resutlt:

Tabell
Lag S V O F TOTAL P
Grupp A
BK Örnen 2 2 0 0 30-10 4
Wåxnäs BC 3 2 0 1 35-25 4
BK Kaskad 2 1 0 1 20-20 2
BK Bågen 3 1 0 2 29-31 2
BK Glam 3 1 0 2 24-36 2
IFK Norrköping BF 3 1 0 2 22-38 2

Grupp B
Uppsala BC 90 3 2 1 0 35-25 5
Ludvika BK 2 2 0 0 25-14 4
Sundbybergs IK F 3 1 1 1 34-26 3
LBK Hudik 3 1 0 2 23-36 2
Domnarvets BS 2 0 1 1 19-21 1
Örta IF 3 0 1 2 23-37 1

Grupp C
Stureby BK 3 2 0 1 39-21 4
Tureberg IF 2 2 0 0 28-12 4
BK Stallis 3 2 0 1 28-31 4
BK Amiki 3 1 0 2 28-31 2
Djurgårdens IF 2 1 0 1 15-25 2
BK Brio 3 0 0 3 21-39 0

Tabel Match:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Match] Script Date: 09/19/2007 17:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Match](
[MatchId] [int] NOT NULL,
[Matchstart] [datetime] NULL,
[LagIdHemma] [int] NOT NULL,
[LagIdBorta] [int] NOT NULL,
[Resultathemma] [int] NULL,
[ResultatBorta] [int] NULL,
CONSTRAINT [PK_Match_1] 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
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Team] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[Team] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Team]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_TeamInGroup] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[TeamInGroup] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_TeamInGroup]

Tabel Team:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Team] Script Date: 09/19/2007 17:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Team](
[TeamId] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamId] 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

Tabel TeamInGroup:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[TeamInGroup] Script Date: 09/19/2007 17:27:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TeamInGroup](
[TeamId] [int] NOT NULL,
[GroupID] [int] NOT NULL,
CONSTRAINT [PK_TeamInGroup_1] PRIMARY KEY CLUSTERED
(
[TeamId] 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
ALTER TABLE [dbo].[TeamInGroup] WITH CHECK ADD CONSTRAINT [FK_TeamInGroup_Grupp] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Grupp] ([GroupId])
GO
ALTER TABLE [dbo].[TeamInGroup] CHECK CONSTRAINT [FK_TeamInGroup_Grupp]

Tabel Group:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Grupp] Script Date: 09/19/2007 17:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grupp](
[GroupId] [int] NOT NULL,
[SeriesId] [int] NOT NULL,
[Namn] [varchar](50) NULL,
CONSTRAINT [PK_Grupp_1] PRIMARY KEY CLUSTERED
(
[GroupId] 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
ALTER TABLE [dbo].[Grupp] WITH CHECK ADD CONSTRAINT [FK_Grupp_Serier] FOREIGN KEY([SeriesId])
REFERENCES [dbo].[Serier] ([SeriesId])
GO
ALTER TABLE [dbo].[Grupp] CHECK CONSTRAINT [FK_Grupp_Serier]

Tabel Series

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Serier] Script Date: 09/19/2007 17:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serier](
[SeriesId] [int] NOT NULL,
CONSTRAINT [PK_Serier] PRIMARY KEY CLUSTERED
(
[SeriesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This is what i dot so far:

SELECT tg.GroupID, t.Name, COUNT(m.ResultatHemma) AS S,
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS V,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS O,
CASE
WHEN m.ResultatHemma - m.ResultatBorta < 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS F,
SUM(m.ResultatHemma) AS Hemma, SUM(m.ResultatBorta) AS Borta,
SUM(m.ResultatHemma - m.ResultatBorta) AS Diff,

CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN 1
ELSE
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN 2
ELSE 0
END END AS P
FROM Match AS m INNER JOIN
Team AS t ON m.LagIdHemma = t.TeamId INNER JOIN
TeamInGroup AS tg ON m.LagIdHemma = tg.TeamId
WHERE (m.MatchId = m.MatchId) AND (tg.GroupID = 2 OR tg.GroupID = 1 OR tg.GroupID = 3)
GROUP BY t.Name, tg.GroupID, m.Resultathemma, m.Resultatborta
ORDER BY P DESC, Diff DESC, S DESC


I can't figure out howto group by t.Name and team in Group A,B, and C

Here are some data From Match:

MatchId Matchstart LagIdHemma LagIdBorta Resultathemma ResultatBorta
----------- ----------------------- ----------- ----------- ------------- -------------
700201001 2007-09-08 10:00:00.000 33768 159120 16 4
700201002 2007-09-08 15:00:00.000 33744 159120 16 6
700201003 2007-09-08 11:00:00.000 33894 33407 16 4
700201004 2007-09-08 14:00:00.000 33874 33407 11 9
700201005 2007-09-08 11:00:00.000 33737 32186 10 10
700201006 2007-09-08 16:20:00.000 30896 32186 15 5
700201007 2007-09-08 11:00:00.000 33286 42031 11 9
700201008 2007-09-08 15:00:00.000 33290 42031 14 5
700201009 2007-09-08 11:00:00.000 33628 33722 13 7
700201010 2007-09-08 15:00:00.000 33684 33722 15 5
700201011 2007-09-08 11:00:00.000 43635 33705 9 11
700201012 2007-09-08 15:00:00.000 31346 33705 16 4
700201013 2007-09-15 11:00:00.000 159120 33768 8 12
700201014 2007-09-15 16:00:00.000 33407 33768 11 9
700201015 2007-09-15 11:00:00.000 33874 33744 9 11
700201016 2007-09-15 13:00:00.000 33894 33744 14 6
700201017 2007-09-15 10:00:00.000 32186 33737 12 8
700201018 2007-09-15 16:00:00.000 42031 33737 13 7
700201019 2007-09-15 11:00:00.000 33290 30896 9 11
700201020 2007-09-15 15:40:00.000 33286 30896 10 10
700201021 2007-09-15 11:00:00.000 33722 33628 9 10
700201022 2007-09-15 16:00:00.000 33705 33628 12 8
700201023 2007-09-15 11:00:00.000 31346 33684 8 12
700201024 2007-09-15 15:00:00.000 43635 33684 16 4

Team:
TeamId Name GroupId
----------- -------------------- -----------
30896 Sundbybergs IK F 2
31346 Turebergs IF 3
32186 Örta IF 2
33286 Domnarvets BS 2
33290 Ludvika BK 2
33407 BK Glam 1
33628 BK Amiki 3
33684 Stureby BK 3
33705 BK Brio 3
33722 BK Stallis 3
33737 Uppsala BC90 2
33744 BK Bågen 1
33768 Wåxnäs BC 1
33874 BK Kaskad 1
33894 BK Örnen 1
42031 LBK Hudik 2
43635 Djurgårdens IF 3
159120 IFK Norrköping 1

(18 row(s) affected)

TeamInGroup:
TeamId GroupID
----------- -----------
30896 2
31346 3
32186 2
33286 2
33290 2
33407 1
33628 3
33684 3
33705 3
33722 3
33737 2
33744 1
33768 1
33874 1
33894 1
42031 2
43635 3
159120 1

(18 row(s) affected)


Group:
GroupId SeriesId Namn
----------- ----------- --------------------------------------------------
1 48967 Grupp A
2 48967 Grupp B
3 48967 Grupp C

(3 row(s) affected)
And Serier:
SeriesId
-----------
48967

(1 row(s) affected)

Best regards

Gerten