Select with subfunction count() problem MS-SQL

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select with subfunction count() problem MS-SQL

Author

Topic

perofoslo
Starting Member

1 Post

Posted - 2007-01-23 : 17:38:23

I'm working on an MS-SQL 2005 server, and feel totally lost on fixing a simple problem
of reading count() from the main Table! I think I did the this easely in SQL 2000? Please;

Tables (Some Columns, Collates and constraints removed to save space):

CREATE TABLE [dbo].[Bruker_Detalj]( ---> User info AS BrukerID
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 4000 Columns now
[KonsulentID] [varchar](5) NOT NULL,
[BrukerID] [int] NOT NULL,
[Etternavn] [varchar](50) NOT NULL,
[Fornavn] [varchar](50) NOT NULL,
...(several Columns removed)

CREATE TABLE [dbo].[Bruker_Tiltak]( ---> User Action AS BrukerID
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 50000 Columns now
[KonsulentID] [varchar](5) NOT NULL,
[BrukerID] [int] NOT NULL,
[StatusID] [int] NOT NULL,
[Status_ar] [int] NOT NULL, ---> Year
[Status_mnd] [int] NOT NULL, ---> Month
...(some Columns removed)

CREATE TABLE [dbo].[Velg_Tiltak]( ---> User Action, Velg = Select
[PostID] [int] IDENTITY(1,1) NOT NULL, ---> About 100 Columns
[KonsulentID] [varchar](5) NOT NULL DEFAULT ('Admin'),
[StatusID] [int] NOT NULL,
[StatusText] [varchar](100) NOT NULL,
[StatusType] [varchar](50) NOT NULL,
...(some Columns removed)

The wanted result is a view with:

Bruker_Tiltak.Status_ar, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText, _
"count(Bruker_Tiltak.(*) AS Status_telle", _
"Bruker_Tiltak.Status_mnd (12 part "pilot" table January-December)", _
(problem: not in code!): _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
AND Bruker_Tiltak.Status_mnd is 1-6 AS FirstHalf, _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
Bruker_TiltakStatus_mnd is 7-12 AS SecondHalf, _
count(DISTINCT Bruker_Tiltak.BrukerID) for Bruker_Tiltak.Status_ar = Bruker_Tiltak.Status_ar _
Bruker_TiltakStatus_mnd is 1-12 AS WholeYear

The "pilot" grouping works fine with a double views:

View Rap_Summering (1):
SELECT TOP (100) PERCENT Bruker_Tiltak.Status_ar, Bruker_Tiltak.Status_mnd,
Velg_Tiltak.StatusType, Velg_Tiltak.StatusText, COUNT(*) AS Status_telle
FROM Bruker_Tiltak INNER JOIN
Velg_Tiltak ON Bruker_Tiltak.StatusID = Velg_Tiltak.StatusID
GROUP BY Bruker_Tiltak.Status_ar, Bruker_Tiltak.Status_mnd, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText
ORDER BY Bruker_Tiltak.Status_ar DESC, Bruker_Tiltak.Status_mnd, Velg_Tiltak.StatusType, Velg_Tiltak.StatusText

View Rap_Sum_Detalj (2):
SELECT TOP (100) PERCENT Status_ar AS År, StatusType AS Status, StatusText AS Tiltak,
SUM(CASE WHEN Rap_summering.Status_mnd = 1 THEN Rap_summering.Status_telle END) AS Jan,
SUM(CASE WHEN Rap_summering.Status_mnd = 2 THEN Rap_summering.Status_telle END) AS Feb,
SUM(CASE WHEN Rap_summering.Status_mnd = 3 THEN Rap_summering.Status_telle END) AS Mar,
SUM(CASE WHEN Rap_summering.Status_mnd = 4 THEN Rap_summering.Status_telle END) AS Apr,
SUM(CASE WHEN Rap_summering.Status_mnd = 5 THEN Rap_summering.Status_telle END) AS Mai,
SUM(CASE WHEN Rap_summering.Status_mnd = 6 THEN Rap_summering.Status_telle END) AS Jun,
SUM(CASE WHEN Rap_summering.Status_mnd = 7 THEN Rap_summering.Status_telle END) AS Jul,
SUM(CASE WHEN Rap_summering.Status_mnd = 8 THEN Rap_summering.Status_telle END) AS Aug,
SUM(CASE WHEN Rap_summering.Status_mnd = 9 THEN Rap_summering.Status_telle END) AS Sep,
SUM(CASE WHEN Rap_summering.Status_mnd = 10 THEN Rap_summering.Status_telle END) AS Okt,
SUM(CASE WHEN Rap_summering.Status_mnd = 11 THEN Rap_summering.Status_telle END) AS Nov,
SUM(CASE WHEN Rap_summering.Status_mnd = 12 THEN Rap_summering.Status_telle END) AS Des
FROM Rap_summering
GROUP BY Status_ar, StatusType, StatusText
ORDER BY År, Status, Tiltak
(SQL 2005 server not enabled for PILOT)

Of some reason I am not able to include the COUNT(DISTINCTs x) into the SELECTs.
This is a WEB app. that should work with any (most) SQLs so I should use "plain" SQL for now.
Any tip would be highly appreciated! Writing this may help me to see the solution tomorrow?

Thanks, Per

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources