SQL 2012 :: Average Salary For Each Department

Jan 17, 2015

I 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;

View 1 Replies


ADVERTISEMENT

Update The Salary Of Each Manager To Be Double The Average Salary Of The Employees He/she Manages

Mar 23, 2006

create 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 Related

Employees And Their Department Who Is Top Salary

Dec 4, 2006

i 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

View 13 Replies View Related

SQL Server 2012 :: Find All Employee Whose Salary Sum Is 80% Of Sum Of Salary Of All Employees

Aug 14, 2014

Let 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 Related

Max Average Salary

May 8, 2012

Select * from personnel

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

And how i find max average salary?

View 8 Replies View Related

SQL Server 2012 :: Cumulative Average Without Cursor

Jul 29, 2015

I am trying to calculate cumulative GPA which is currently it is being done via a cursor and i was looking to change it to set based if possible.

The cumulative GPA is basically the average marks of all courses in a term and all previous terms. Where I am having an issue is if the course was taken in a previous term and the mark was lower, then the lower mark in not factored in to the GPA. If a course was taken in a previous term and the mark was higher then the marks for the current term are not factored into the GPA.

In the example below, math is taken in all three terms and the current term's mark is included in the GPA but not the previous terms because current terms mark is higher. Two classes (bio,chem) are taken in third term but not included due to marks being higher in second term.

CREATE TABLE [gpa]([term] int,
[course] varchar(10),
[mark] float);
INSERT INTO [gpa]
VALUES (1,'math',3),
(1,'eng',4),

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Trying To Add Third Grouping Set To Return Average Aggregate

Feb 16, 2014

i'm building a query to return metrics that will drive 3 seperate pivot tables showing

1. Total count of LineItems per D_Type each month
2. Total count of LineItems per Status each month
3. Avg count of LineItems per Invoice each month

I am able to get the first two, but having hard time with the 3rd.

Here's some representative ddl
create table Remediation
(Invoice nvarchar(10), D_Type nvarchar(20), Status nvarchar(20), RemediationDate datetime);

insert into Remediation values
--this will create data for Jan, 2014
('501', 'Recycle', 'Pass', getdate()-30),
('501', 'Reuse', 'Pass', getdate()-30),
('501', 'Remarket', 'Fail', getdate()-30),

[code]....

how to add the average metric to this query?

View 9 Replies View Related

SQL 2012 :: Query To Match Multiple Results And Average

Mar 3, 2014

I work for a school district and new requirement we were just given for scoring some student scores.

Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.

What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.

View 9 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation 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.

View 2 Replies View Related

Department In Respect To Language

Aug 16, 2007

I have to Make Department Table.
But this there names can be in multi language.
--------------------------------------------DepartmentGroup--------------------------------------------DepartmentGroupId (P.K.)UniqueNameCreatedDateModifiedDate--------------------------------------------
--------------------------------------------Department--------------------------------------------DepartmentId (P.K.)NativeNameNavigateUrlIsFavoriteLanguageId (F.K.)DepartmentGroupId (F.K.)IsVisibleCreatedDateModifiedDate--------------------------------------------
 
From this design we can move to same department in different language.
 
is it right?

View 2 Replies View Related

Department Wise Max And Min Salaried Employees

Sep 14, 2014

I have the employees and department tables(structure below).

Write a sql to get the department name,employee earning maximum salary,employee earning minimum salary for each department

result set:

dname |max_salaried_employee|min_salaried_employee
------------------------------------------------------
Accounts | Blakes | Miller
HR | King | James

Structure :

create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)

[Code] .....

View 1 Replies View Related

Display Paper Co Authored From Same Department

Nov 27, 2006

How to display the paper number that is co authored by authors from the same department?
The database structure:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)
Thanks for help a lot

View 3 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I 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

View 4 Replies View Related

Analysis :: Dynamic Measure Value Based Upon Role / Department

Sep 28, 2015

I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:

- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.

Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department. 

It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.

View 3 Replies View Related

SQL Server 2008 :: Roster Rotating Persons Within Each Department For Available Dates

May 29, 2015

I need to prepare a roster rotating persons within each Department for the available dates

Please refer to the below sample tables and data

CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department

[Code] ....

Expected Output

SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person

[Code] .....

View 9 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:

Year
Month
Date

And the following row groups:

Region
Product
SubType (hidden, data at the date level is summed to Product)

At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

View 0 Replies View Related

Need -&> Max(sum(salary)) Query

Mar 20, 2004

hi

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

View 7 Replies View Related

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?

View 15 Replies View Related

Max Salary Query

Mar 19, 2007

Hi

How to get the maximum salry from a table without using top and
aggreate function

thanks

asm

View 4 Replies View Related

SQL To Increment Salary

Sep 26, 2007

i 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)

View 20 Replies View Related

How To Write A Query For 1st, 2nd, 3rd Max Salary

Apr 18, 2006

employee table having columns employeeid, salary

i want to write a query to get 1st, 2nd and 3rd max salary ?

View 2 Replies View Related

SQL Server DBA Salary Survey

Aug 29, 2006

I 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 Related

How To Sum All Salary In A Year Given To Employees

Dec 8, 2011

I 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 ?

View 3 Replies View Related

How To Know When Total Salary Is At Certain Amount

Jul 24, 2013

I 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

View 4 Replies View Related

Trigger To Increment The Salary By 10%

Sep 25, 2007

i 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

View 2 Replies View Related

Calculating Salary Per Hour?

Jan 26, 2008

is 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

View 2 Replies View Related

TRigger To Increment Salary By 10%

Sep 26, 2007

aa

View 12 Replies View Related

How To Get Top Three Salary Getters From Table Employee

Mar 1, 2007

Dear 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 
  

View 1 Replies View Related

What Is The Query To Find A 5th Highest Salary With Sqlserver.

Aug 9, 2005

What 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 Related

Stored Procedure To Calculate Month Salary(urgent)

Aug 30, 2005

i 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 Related

Average

Sep 26, 2007

Hello,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 Related

Average Help

Apr 18, 2006

if 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

View 8 Replies View Related

Average?

Jun 19, 2007

I 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)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved