Aggregate Function Problems - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Aggregate Function Problems

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-30 : 18:51:07

Hi all I want to be able to include the Dates into the SELECT section of this procedure but when I keep gettting an error message. And if I add it to the Select statemtent along with the GROUP BY it doesnt give me the correct data. I would like for the users to see [IR_Month] and [IR_Year] in the select statment so that they know thats exactly what they are getting. Does that make sense?? How do I incorporate those two fields??

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[YrlyActionTotals]
@Start_IR_Month varchar(4) = NULL,
@End_IR_Year varchar(4) = NULL
AS

SELECT  [Action Type], Count([Action Type]) AS [CountOfAction Type]
FROM dbo.Revised_MainTable
WHERE (@Start_IR_Month is null or [IR_Month] = @Start_IR_Month)
	AND (@End_IR_Year is null or [IR_Year] = @End_IR_Year)
GROUP BY [Action Type]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 00:00:36

did you mean this?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[YrlyActionTotals]
@Start_IR_Month varchar(4) = NULL,
@End_IR_Year varchar(4) = NULL
AS

SELECT  [Action Type],[IR_Month],[IR_Year],
 Count([Action Type]) AS [CountOfAction Type]
FROM dbo.Revised_MainTable
WHERE (@Start_IR_Month is null or [IR_Month] = @Start_IR_Month)
	AND (@End_IR_Year is null or [IR_Year] = @End_IR_Year)
GROUP BY [Action Type],[IR_Month],[IR_Year]

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-31 : 11:37:40

When I run it I get the wrong calculations, for 2005 BOL should be 345 not 31. So the calculations are way off and I dont understand why? If I keep the procedure this way my calcualtions are correct

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[YrlyActionTotals]
@Start_IR_Month varchar(4) = NULL,
@End_IR_Year varchar(4) = NULL
AS

SELECT  [Action Type], Count([Action Type]) AS [CountOfAction Type]
FROM dbo.Revised_MainTable
WHERE (@Start_IR_Month is null or [IR_Month] = @Start_IR_Month)
	AND (@End_IR_Year is null or [IR_Year] = @End_IR_Year)
GROUP BY [Action Type]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:42:11

what are types of values existing in IR_Month & IR_Year? are they datetime values?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-31 : 11:55:42

Oh sorry Viskh16 I ran on report services put some grouping and in there along with a =Sum(Fields!CountOfAction_Type.Value) on the footer of the report and there ya go 345 just like its suppose to be. Sorry about that AGAIN thank you so much for your help again Viskah16.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-31 : 11:56:06

no they are Varchar(4)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:58:31

quote:
Originally posted by muzzettemm

Oh sorry Viskh16 I ran on report services put some grouping and in there along with a =Sum(Fields!CountOfAction_Type.Value) on the footer of the report and there ya go 345 just like its suppose to be. Sorry about that AGAIN thank you so much for your help again Viskah16.

ok Cheers
Glad that you sorted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources