max avg salary - 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

General SQL Server Forums

New to SQL Server Programming

max avg salary

Author

Topic

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-08 : 16:25:31

hi everyone

select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)

and how i find max average salary?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-08 : 16:29:00

[code]SELECT TOP 1 AVG(Salary) AS AvgSalary
FROM personnel
GROUP BY bolno
ORDER BY AVG(Salary) DESC
[/code]

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-08 : 17:01:11

thanks =)
sorry but How do I show the other fields?

--select * from personel--

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 17:02:14

quote:
Originally posted by johnny1walker

thanks =)
sorry but How do I show the other fields?

--select * from personel--


SELECT TOP 1 AVG(Salary) AS AvgSalary,*
FROM personnel
GROUP BY bolno
ORDER BY AVG(Salary) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-08 : 17:07:50

Msg 8120, Level 16, State 1, Line 1
Column 'personel.sicil' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 19:45:41

quote:
Originally posted by johnny1walker

Msg 8120, Level 16, State 1, Line 1
Column 'personel.sicil' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT TOP 1 WITH TIES p.*,AvgSalary
FROM personnel p 
INNER JOIN (SELECT bolno, AVG(Salary) AS AvgSalary
            FROM personnel
            GROUP BY bolno
            )q
ON q.bolno = p.bolno
ORDER BY AvgSalary DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-10 : 05:46:28

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

quote:

Sicil AD SOYAD ADRES BRUT MAAS BOLNO
25 Eslem Nur Aydin 400 400 9
26 Fatih Kaya izmir 550 450 10
12 ali kaya tavsanli 1.sok 750 700 3
13 ömer ak tavsanli 2. sok 800 800 4
14 veli beyaz tavsanli 3.sok 900 850 5
15 ayse siyah tavsanli 4.sok 750 700 4
16 ahmet toruk mah tavsanli 5.sok 700 650 5
17 mehmet kamber tavsanli 900 800 6
18 tarik kaya NULL 500 450 2
19 osman pembe NULL 600 550 3
20 nuri kolcak NULL 650 600 4
21 ali canip kütahya tavsanli k 1000 900 2
22 semih demir tavsanli a sok. b cad 850 850 4
23 Sule mantar kütahya 750 850 2
25 Eslem Nur Aydin 400 400 9
26 Fatih Kaya izmir 550 450 10
12 ali kaya tavsanli 1.sok 750 700 3
13 ömer ak tavsanli 2. sok 800 800 4
14 veli beyaz tavsanli 3.sok 900 850 5
15 ayse siyah tavsanli 4.sok 750 700 4
16 ahmet toruk mah tavsanli 5.sok 700 650 5
17 mehmet kamber tavsanli 900 800 6
18 tarik kaya NULL 500 450 2
19 osman pembe NULL 600 550 3
20 nuri kolcak NULL 650 600 4
21 ali canip kütahya tavsanli k 1000 900 2
22 semih demir tavsanli a sok. b cad 850 850 4
23 Sule mantar kütahya 750 850 2
25 Eslem Nur Aydin 400 400 9
26 Fatih Kaya izmir 550 450 10
12 ali kaya tavsanli 1.sok 750 700 3
13 ömer ak tavsanli 2. sok 800 800 4
14 veli beyaz tavsanli 3.sok 900 850 5
15 ayse siyah tavsanli 4.sok 750 700 4
16 ahmet toruk mah tavsanli 5.sok 700 650 5
17 mehmet kamber tavsanli 900 800 6
18 tarik kaya NULL 500 450 2
19 osman pembe NULL 600 550 3
20 nuri kolcak NULL 650 600 4
21 ali canip kütahya tavsanli k 1000 900 2
22 semih demir tavsanli a sok. b cad 850 850 4
23 Sule mantar kütahya 750 850 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 12:16:25

do you mean you want to show only max avg salary amount and not other details of record containing it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-10 : 13:53:48

yes,example
13 ömer ak tavsanli 2. sok 800 800 4

i find some code, but there are error!
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
                ))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:18:27

quote:
Originally posted by johnny1walker

yes,example
13 ömer ak tavsanli 2. sok 800 800 4

i find some code, but there are error!
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
))

whats the problem with code i posted?


SELECT TOP 1 AvgSalary
FROM personnel p 
INNER JOIN (SELECT bolno, AVG(Salary) AS AvgSalary
            FROM personnel
            GROUP BY bolno
            )q
ON q.bolno = p.bolno
ORDER BY AvgSalary DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnny1walker
Starting Member

17 Posts

Posted - 2012-05-11 : 05:19:52

thanks.no problm but
person to show code
may want to see the other codes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources