Accessing column data in database and finding the avg,min,max of salary attribute in Employee table [Resolved]

Posted by Chvrsri under Sql Server on 10/22/2010 | Points: 10 | Views : 2099 | Status : [Member] [Moderator] [MVP] | Replies : 5
Hi all,

my table format is in this form ::
empname salary merit
abc 1000 good
def 2000 good
hjg 3000 good
xyz 0 bad


Now i want to display in this format

good maximum(in percentage)
minimum(in percentage)
average(in percentage)

bad maximum(in percentage)
minimum(in percentage)
average(in percentage)

these 3 values must be calculated considering all the employees.

That means how many maximum persons have good merit, minimum persons having good merit,average persons having good merit...
Similarly with bad merit...

It must be in a genric way such that in future i may increase merit type i wnt be only for good and bad.... Hope all understood my problem...

Help is greately appreciated.Thanks in advance


With Regards
CH.V.Radha Srikanth

Thanks,
Radha Srikanth



Responses

Posted by: T.saravanan on: 10/22/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi Srikanth,

Using StoredProcedure you can achieve it.I think you said based on salary.
Here i am give some idea for your reference.

Declare @maxgood decimal

Declare @countgood decimal
Set @maxgood =(Select Max(Salary) from table1 where merit='good')
Set @countgood=(Select Count(*) from table1 where merit='good' and Salary=@maxgood)
Select (@maxgood/ @countgood) AS [PerofMaxGood] // You got Percentage of Maximum Good


But you told in future merit type will be increase.
So first using temp table you get all the type of merit and then to pass one by one into the line
Select Max(Salary) from table1 where merit=@merit

If any doubt ask to me i will help you.

Cheers :)



Thanks,
T.Saravanan

Chvrsri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Chvrsri on: 10/25/2010 [Member] [Moderator] [MVP] Silver | Points: 25

Up
0
Down
Thanq so much for the response but still there is an issue.I need to represent those values in terms of rows.
That means in this way

Good
Max=Maximum Good Percentage
Min=Minimum Good Percentage
Avg=Average Good Percentage

Bad
Max=Maximum Bad Percentage
Min=Minimum Bad Percentage
Avg=Average Bad Percentage

Using a Single Query even it is fine using a Stored Procedure



Thanks,
Radha Srikanth

Chvrsri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 10/25/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Srikanth,

What did you try? Can you give your code here?

Thanks,
T.Saravanan

Chvrsri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Chvrsri on: 10/25/2010 [Member] [Moderator] [MVP] Silver | Points: 25

Up
0
Down
I cannot share the actual code because it is Confidential . Even i just want the logic. To implement internal functionality of my project. Hope u understand.
Any how i got the answer,, i did that using the approach u said

i created a temporary table where in which i used variables to update my percentage score for different merits
and then applied a while loop to loop through the values of the salaries and applied the attributes MIN,MAX,AVG
by this way i achived my requirement.

Iam very much thank ful to you, u gave me the idea of solving this .

Cheers!!!!

Thanks,
Radha Srikanth

Chvrsri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 10/25/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Srikanth,

Ok.Its not a problem.
Good, I am also said this way of approach.Any how you got an answer.I am happy.

Cheers :)



Thanks,
T.Saravanan

Chvrsri, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response