Distinct Count Query

[kloner]

Hi guys,

I would appreciate any helpon this one as I am pulling out my hair trying to get it to work.

What I have got so far is a recordset with all the correct distict data. The query is below.

What I am after is to also return the count of each distinct record.

SQL Query:

SELECT 
	DISTINCT [FunctionName], 
	[ModuleName], 
	[ErrorCode], 
	[ErrorDescription]
FROM 
	[dbo].[Tbl_Error]

Returned Data:

ClArticle      CheckAdd     -214   [DBNETLIB]
ClArticle      LoadInfo     -214   Object req
ClArticle      VersionLoad  -214   [DBNETLIB04]
ClArticleMain  Load         -29    [DBNETLIB999]
CLMatt	       jkj          123    ffff
ClMatthew      GetOutOfHere 123    AAA You m
ClMatthew      GetOutOfHere 135    You must
ClUser         IsActive     430    Report:=
ClUser	       LogIn        440    Username

As you can see each row is UNIQUE. I am trying to add a counter column onto the end of each row to show how many times that error occured.

So expected output would be:

ClArticle      CheckAdd     -214   [DBNETLIB]     12
ClArticle      LoadInfo     -214   Object req     58
ClArticle      VersionLoad  -214   [DBNETLIB04]   95
ClArticleMain  Load         -29    [DBNETLIB999]  10
CLMatt	       jkj          123    ffff           6
ClMatthew      GetOutOfHere 123    AAA You m      45
ClMatthew      GetOutOfHere 135    You must       46
ClUser         IsActive     430    Report:=       55
ClUser	       LogIn        440    Username       19

I have tried doing all sorts of things (distinct counts and groupby?) without any luck.

Any help or tips would be great!

Thanks guys and girls.

Matt

 

[kloner]

Ah I finally worked it out! Thanks anyway guys! Happy I answered my own question ...

SELECT
	[FunctionName], 
	[ModuleName], 
	[ErrorCode], 
	[ErrorDescription], 
	COUNT(*) AS ErrorCount
FROM 
	[dbo].[Tbl_Error]
GROUP BY 
	[FunctionName], 
	[ModuleName], 
	[ErrorCode], 
	[ErrorDescription]
ORDER BY 
	[FunctionName], 
	[ModuleName], 
	[ErrorCode], 
	[ErrorDescription]

Matt

kloner