Max Average Salary
May 8, 2012Select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
And how i find max average salary?
ADVERTISEMENT
Update The Salary Of Each Manager To Be Double The Average Salary Of The Employees He/she Manages
Mar 23, 2006create table employee(empid int,empname varchar(20),managerid int notnull, sal int)insert into employee values(1,'ranga',22,5000)insert into employee values(2,'satish',22,8000)insert into employee values(3,'sunil',11,4500)insert into employee values(4,'sridhar',22,2000)insert into employee values(5,'ramesh',33,12000)insert into employee values(6,'srini',22,16000)insert into employee values(7,'sashi',33,54000)insert into employee values(8,'rajani',22,71000)insert into employee values(9,'praveen',11,6060)insert into employee values(10,'bhaskar',22,11120)insert into employee values(11,'baba',33,9000)create table employment (managerid int,managername varchar(20),sal int)insert into employment values(11,'rob',2500)insert into employment values(22,'babu',5000)insert into employment values(33,'ram',6000)now my problem isUpdate the salary of each manager to be double the averagesalary of the employees he/she managespls helpsati
View 3 Replies View RelatedSQL 2012 :: Average Salary For Each Department
Jan 17, 2015I would like to find the average salary for each department which has min salary
In my case I will have 3 departments which have min salary.
select distinct d.department_name, E.SALARY, avg(E.salary)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.SALARY = (SELECT MIN(E.SALARY)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID))
GROUP BY D.DEPARTMENT_NAME, E.SALARY;
SQL Server 2012 :: Find All Employee Whose Salary Sum Is 80% Of Sum Of Salary Of All Employees
Aug 14, 2014Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.
View 4 Replies View RelatedAnalysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX
Jun 21, 2015Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
Need An Average By Year Of An Average By Month
Feb 15, 2008I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?
Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');
with a result of:
NAME_________________CO
-------------------- --
OFFUTT AFB___________US
MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
Need -&> Max(sum(salary)) Query
Mar 20, 2004hi
i have a table employee:
dept ename salary
---- --------- -------
10 A 2500
20 B 3500
30 C 4000
20 D 5500
10 E 4500
30 F 5200
FIRST QUERY:
select dept,sum(salary) from employee group by dept
the above one is working fine..
after working the first query output,
i want to select the dept,max(sum(salary)) from the table...
how?? could any one send me immediately...
thanks in advance
Hiked Salary...
Oct 20, 2005--Table Empmaster:-
create table Empmaster
(
empid int identity(1,1) constraint pkempid primary key clustered,
empname varchar(10),
empsalary numeric
)
insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)
--Table Salary:-
create table Salary
(
salid int identity(1,1) constraint pksalid primary key clustered,
empid int constraint fkempid foreign key references Empmaster(empid),
dos varchar(10),
salary numeric
)
insert Salary(empid,dos,salary)values('1','2005-08-01','5000')
insert Salary(empid,dos,salary)values('2','2005-08-01','5000')
insert Salary(empid,dos,salary)values('1','2005-09-01','5000')
insert Salary(empid,dos,salary)values('2','2005-09-01','7000')
insert Salary(empid,dos,salary)values('1','2005-10-01','7000')
insert Salary(empid,dos,salary)values('2','2005-10-01','7000')
i have two tables with relations. how do i find out whose salary has been hiked for the 9th month?
Max Salary Query
Mar 19, 2007Hi
How to get the maximum salry from a table without using top and
aggreate function
thanks
asm
SQL To Increment Salary
Sep 26, 2007i want to write a SQL statement to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEmployee(TestNo,EmployeeID,Hrs)
Test(TestNo,TestDate,Result)
How To Write A Query For 1st, 2nd, 3rd Max Salary
Apr 18, 2006employee table having columns employeeid, salary
i want to write a query to get 1st, 2nd and 3rd max salary ?
SQL Server DBA Salary Survey
Aug 29, 2006I am going into salary negotiationyearly review next month after my week off and I am trying to determine what to ask for. I am thinking another 10K and an extra week off would not be unreasonable, but I wanted to get some idea of what you guys think I should ask for in terms of pay.I have been developing software for seven years and I have been a dba for a little over five. I live in pricey northern Virginia. On a fairly regular basis I do about 55 to 60 hour weeks. On my DBA team, I am the only one who can handle both development and production tasks. The others are strictly developers. Although I have been relieved of most of my customer support tasks by our newbie, the customer support manager still brings the nastier bits to me. It is my perception that the more complex tasks get assigned to me. I get told on a regular basis that I am the best dba this place has ever had and other embarrassing accolades are regularly thrown my way. After a year, other than my boss I am the dba that has been here the longest in high turnover high burnout company. This year as we try to move to a SAP model, it looks like we will be going to 24/7 support on a disaster recovery model I am designing and implementing, so I guess I am getting the pager.So how much money should I be asking for?you can PM me with a number if you want.
View 1 Replies View RelatedHow To Sum All Salary In A Year Given To Employees
Dec 8, 2011I need to calculate the salary given to all employees in a year
Code:
select sum(emp_total_sal)from emp_salary
How to modify this code to get what i need ?
How To Know When Total Salary Is At Certain Amount
Jul 24, 2013I would like to know how to use a criterion on this example. I want to know only when the total salary is at a certain amount
SELECT SUM (salary) as Total Salary
FROM employees
WHERE Total Salary > 25000; ---this is where i am having issue
Employees And Their Department Who Is Top Salary
Dec 4, 2006i have 2 tables emp and dept
emp has columns:
empid(pk),empname,deptid(fk),salary
dept has columns:
deptid(pk),deptname
now my aim is:
List of the employees and their department who is top salary earner of the department.
wht i can think of is:
select distinct empname,deptname,max(salary) as 'max salary'
from emp e,dept d
where e.deptid=d.deptid
group by empname,deptname
but it gives unexpected result...
help appreciated
cheers
Trigger To Increment The Salary By 10%
Sep 25, 2007i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)
Hussain
Calculating Salary Per Hour?
Jan 26, 2008is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?
Shimi
TRigger To Increment Salary By 10%
Sep 26, 2007aa
View 12 Replies View RelatedHow To Get Top Three Salary Getters From Table Employee
Mar 1, 2007Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks
What Is The Query To Find A 5th Highest Salary With Sqlserver.
Aug 9, 2005What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.
View 11 Replies View RelatedStored Procedure To Calculate Month Salary(urgent)
Aug 30, 2005i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
View 2 Replies View RelatedAverage
Sep 26, 2007Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel
View 2 Replies View RelatedAverage Help
Apr 18, 2006if I have a bunch of times, and want an average of them, how do I do that?
Thanks.
For more details, see this thread:
http://www.dbforums.com/showthread.php?t=1215633
Average?
Jun 19, 2007I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....
(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)
as
select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,
sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount
(how do I add in the average for both months? The column is orders.ordertotal)
Getting Average?
Jan 6, 2008Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?
code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99
How To Get Average Of Average.
Jan 19, 2008
Hi !
I am having this problem. I have a grouping someting like this.
Group1
Resolved Resolution Time Average Resolution Time
30 2904.46 96.82
54 1442.03 26.70
0 0.00 0.00
0 0.00 0.00
------------------------------
Avg : 30.88
Now to calculate the average inside the grouping I have used this
iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))
I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.
Any ideas will be greatly appreciated.
Thanks in advance.
--Abbi.
Average
May 21, 2008Hi All,
I was hoping someone could help me with pluging moving average into my report.
This is the template I am using for moving average:
//
/*Returns the average value of a member over a specified time interval.*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]
AS Avg
(
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>]
)
// This calculation returns the average value of a member over the specified time interval.
FORMAT_STRING = "Standard";
This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.
WITH
MEMBER [Measures].[Date Key] AS
[Date].[Date].CurrentMember.UniqueName
SELECT
{
[Measures].[Date Key],
[Measures].[ComplexityRank]
} ON COLUMNS,
(
[Work Item].[System_State].[System_State],
(StrToMember(@StartDateParam):StrToMember(@EndDateParam))
)
ON ROWS
FROM [Team System]
WHERE
(
STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
STRTOSET(@WorkItemTypeParam)
)
What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)
WITH
MEMBER
[Measures].[Date Key] AS
[Date].[Date].CurrentMember.UniqueName,
[Measures].[Moving Average] as
AVG({[Date].[Date].currentmember.lag(@IterationParam):
[Date].[Date].currentmember},[Measures].[ComplexityRank])
SELECT
{
{measures.[moving average],measures.[Internet Total Product Cost]} on 0,
[Date].[Date]. Members
[Measures].[Date Key],
[Measures].[ComplexityRank]
} ON COLUMNS,
(
[Work Item].[System_State].[System_State],
(StrToMember(@StartDateParam):StrToMember(@EndDateParam))
)
ON ROWS
FROM [Team System]
WHERE
(
STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
STRTOSET(@WorkItemTypeParam)
Does anyone have any suggestions?
Thanks,
Nici
Getting An Average From A Table
Jan 5, 2007I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.
View 4 Replies View RelatedMoving Average
Oct 25, 2000Can any one be able to help me to write a querry on Moving Average
Example
Product Volume
Fish
Average Of Middle 90%
Sep 21, 2007Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View 9 Replies View RelatedFinding Average
Apr 9, 2008Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........
The codes that i tried..
declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end
Average With Condition
May 28, 2008Problem
fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5
how do i find the average of fld ascending and descending where year=2007
Your help will be appreciated
How To Get Average Number
Jul 12, 2013How do you get the average number in sql?
View 11 Replies View Related