counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.