May 8, 2012 at 2:22 pm
hi everyone
select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
and how i find max average salary?
May 8, 2012 at 2:32 pm
johnny1walker (5/8/2012)
hi everyoneselect * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
and how i find max average salary?
What is a max average salary? Do you some sort of grouping to use to determine the max average? Like maybe by department or something?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 1:08 am
May 10, 2012 at 3:49 am
I have my table here.((quote))
and I just want to show what salary(maas) = 800
maas(salary) = 800 ==> max avarege salary(maas)
--------------
I need a code but it really does not work like this _>
select * from personnel
where maas > MAX(SELECT AVG(maas) from personel group by bolno)
Is there a code similar to this
Sicil ADSOYADADRES BRUT MAAS BOLNO
25Eslem Nur Aydin 4004009
26Fatih Kaya izmir 55045010
12ali kaya tavsanli 1.sok 7507003
13ömer ak tavsanli 2. sok 8008004
14veli beyaz tavsanli 3.sok 9008505
15ayse siyah tavsanli 4.sok 7507004
16ahmet toruk mah tavsanli 5.sok 7006505
17mehmet kamber tavsanli 9008006
18tarik kaya NULL5004502
19osman pembe NULL6005503
20nuri kolcak NULL6506004
21ali canip kütahya tavsanli k 10009002
22semih demir tavsanli a sok. b cad 8508504
23Sule mantar kütahya 7508502
25Eslem Nur Aydin 4004009
26Fatih Kaya izmir 55045010
12ali kaya tavsanli 1.sok 7507003
13ömer ak tavsanli 2. sok 8008004
14veli beyaz tavsanli 3.sok 9008505
15ayse siyah tavsanli 4.sok 7507004
16ahmet toruk mah tavsanli 5.sok 7006505
17mehmet kamber tavsanli 9008006
18tarik kaya NULL5004502
19osman pembe NULL6005503
20nuri kolcak NULL6506004
21ali canip kütahya tavsanli k 10009002
22semih demir tavsanli a sok. b cad 8508504
23Sule mantar kütahya 7508502
25Eslem Nur Aydin 4004009
26Fatih Kaya izmir 55045010
12ali kaya tavsanli 1.sok 7507003
13ömer ak tavsanli 2. sok 8008004
14veli beyaz tavsanli 3.sok 9008505
15ayse siyah tavsanli 4.sok 7507004
16ahmet toruk mah tavsanli 5.sok 7006505
17mehmet kamber tavsanli 9008006
18tarik kaya NULL5004502
19osman pembe NULL6005503
20nuri kolcak NULL6506004
21ali canip kütahya tavsanli k 10009002
22semih demir tavsanli a sok. b cad 8508504
23Sule mantar kütahya 7508502
May 10, 2012 at 3:56 am
I still don't quite get your criteria, but are you looking for something like this:
SELECT *
FROM personnel
WHERE maas > ( SELECT TOP 1
AVG(maas)
FROM personel
GROUP BY bolno
ORDER BY AVG(maas) desc
)
E.g. all personnel that have a salary greater than the highest average salary of a "bolno" group?
Or, are you trying to get all employees that have a salary higher than the average salary within their own salary group? In which case, maybe something like:
SELECT *
FROM personnel a
INNER JOIN ( SELECT bolno ,
AVG(maas) avg_maas
FROM personnel
GROUP BY bolno
) b ON a.bolno = b.bolno
WHERE maas > avg_maas
It would be easier to help you with DDL and expected results as has been asked
May 10, 2012 at 4:07 am
thanks..Only the highest average salary(maas) = (800).. I want to show
eg.
Sicil ad soyad adres brut maas bolno
13 ömer ak tavsanli 2. sok 800 800 4
May 10, 2012 at 4:15 am
I don't think you're explaining yourself very well, but I'll take one more stab at it:
SELECT *
FROM personnel
WHERE maas = ( SELECT TOP 1
AVG(maas)
FROM personel
GROUP BY bolno
ORDER BY AVG(maas) desc
)
You're contradicting yourself between the various posts and are switching between saying greater than and equals. This would be all people who's salary is equal to the maximum average salary within a group.
May 10, 2012 at 4:24 am
thanks a lot =)
May 11, 2012 at 3:22 am
sorry
error codes have the following
Would you look at
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
error and
max(avg_sal) --> invalid column name
select *
from personel
where maas = (select max(avg_sal)
from (
SELECT AVG (maas) as avg_sal
from personel
group by bolno
))
May 11, 2012 at 5:23 am
You need to give your derived table an alias (I've used "tbl" here, but anything will do):
select *
from personel
where maas = (select max(avg_sal)
from (
SELECT AVG (maas) as avg_sal
from personel
group by bolno
) tbl )
May 11, 2012 at 8:02 am
alvirasingh2012 (5/11/2012)
these are the aggregate function.they are reduce coding and easy to use.
spam reported
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 1:37 pm
thanks Gazareth =)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply