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 : 3431 | Status : [Member] [Moderator] [MVP] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

0	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

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

0	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

Latest Posts