March 29, 2012 at 10:01 am
I would like a query which shows avg salary of all teachers and assistants, by school as in the example below. (I have provided sample tables below). What kind of aggregation statement should I be trying for this, a pivot??
AVG_SALARY
SCHOOL_1
TEACHER 50,000
ASSIST 25,000
SCHOOL_2
TEACHER 60,000
ASSIST 21,000
----DDL
create database Sandbox
GO
use sandbox
go
CREATE TABLE SCHOOL
(SCHOOL_ID INT PRIMARY KEY NOT NULL,
SCHOOL_NAME VARCHAR(30))
INSERT INTO SCHOOL
VALUES
(1, 'MARIGOLD'),
(2, 'SPECTRUM'),
(3, 'JACKSON'),
(4, 'ROYALVIEW');
CREATE TABLE TEACHER
(TEACHER_ID INT PRIMARY KEY NOT NULL,
SCHOOL_ID INT NOT NULL,
TEACHER_NAME VARCHAR(30),
SALARY FLOAT
FOREIGN KEY (SCHOOL_ID) REFERENCES SCHOOL(SCHOOL_ID));
INSERT INTO TEACHER
VALUES
(1, 1, 'WRIGHT', 50000),
(2, 2, 'MEIKLE', 60000),
(3, 3, 'JOHNSON', 51000),
(4, 4, 'EDWARDS', 49000),
(5, 4, 'CAMPBELL', 40000);
CREATE TABLE ASSISTANT
(ASSIST_ID INT PRIMARY KEY NOT NULL,
SCHOOL_ID INT NOT NULL,
ASSIS_NAME VARCHAR(30),
SALARY FLOAT,
FOREIGN KEY (SCHOOL_ID) REFERENCES SCHOOL(SCHOOL_ID));
INSERT INTO ASSISTANT
VALUES
(1, 1, 'SABRINA', 25000),
(2, 2, 'LORADONNA', 21000),
(3, 3, 'MISTY', 26000),
(4, 4, 'LYNNE', 20000),
(5, 4, 'BARB', 29000);
-
March 29, 2012 at 10:10 am
Thank you for posting proper DDL and DML so we can see what you see.
For your query needs look up GROUP BY, and the AVG aggregate.
Code re-posted with formatting:
----DDL
CREATE DATABASE Sandbox
GO
USE sandbox
go
CREATE TABLE SCHOOL
(
SCHOOL_ID INT PRIMARY KEY
NOT NULL,
SCHOOL_NAME VARCHAR(30)
)
INSERT INTO SCHOOL
VALUES (1, 'MARIGOLD'),
(2, 'SPECTRUM'),
(3, 'JACKSON'),
(4, 'ROYALVIEW');
CREATE TABLE TEACHER
(
TEACHER_ID INT PRIMARY KEY
NOT NULL,
SCHOOL_ID INT NOT NULL,
TEACHER_NAME VARCHAR(30),
SALARY FLOAT FOREIGN KEY (SCHOOL_ID) REFERENCES SCHOOL (SCHOOL_ID)
);
INSERT INTO TEACHER
VALUES (1, 1, 'WRIGHT', 50000),
(2, 2, 'MEIKLE', 60000),
(3, 3, 'JOHNSON', 51000),
(4, 4, 'EDWARDS', 49000),
(5, 4, 'CAMPBELL', 40000);
CREATE TABLE ASSISTANT
(
ASSIST_ID INT PRIMARY KEY
NOT NULL,
SCHOOL_ID INT NOT NULL,
ASSIS_NAME VARCHAR(30),
SALARY FLOAT,
FOREIGN KEY (SCHOOL_ID) REFERENCES SCHOOL (SCHOOL_ID)
);
INSERT INTO ASSISTANT
VALUES (1, 1, 'SABRINA', 25000),
(2, 2, 'LORADONNA', 21000),
(3, 3, 'MISTY', 26000),
(4, 4, 'LYNNE', 20000),
(5, 4, 'BARB', 29000);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 10:18 am
Thank you opc.three.
First of all, what do you use to format the DDL? I like the way you've posted it within window/scroll bar and would like to be able to do same in future.
Here's query using group by and avg. But as you can see, when there are multiple teachers the school name gets listed multiple times. I would like each school listed once, and teachers in the school to be grouped under respective schools.
select school_name, assis_name, teacher_name,
avg(teacher.salary) as teacher_salary, avg(assistant.salary) as assis_salary
from school
join assistant on
school.school_id = assistant.school_id
join teacher on
school.school_id = teacher.teacher_id
group by school_name, teacher_name, assis_name
-
March 29, 2012 at 10:33 am
aitchkcandoo (3/29/2012)
Thank you opc.three.First of all, what do you use to format the DDL? I like the way you've posted it within window/scroll bar and would like to be able to do same in future.
Surround your code with these special tags:
[code="sql"][/code] and the code will be formatted on Forum postings. There are shortcuts on the left side of the posting window. You can optionally highlight your code in the posting window and click the appropriate shortcut to automatically surround it. Use the Preview button to play with the options.
Here's query using group by and avg. But as you can see, when there are multiple teachers the school name gets listed multiple times. I would like each school listed once, and teachers in the school to be grouped under respective schools.
select school_name, assis_name, teacher_name,
avg(teacher.salary) as teacher_salary, avg(assistant.salary) as assis_salary
from school
join assistant on
school.school_id = assistant.school_id
join teacher on
school.school_id = teacher.teacher_id
group by school_name, teacher_name, assis_name
Something like this?
SELECT school_name,
'teacher' AS position,
AVG(teacher.salary) AS average_salary
FROM school
JOIN teacher ON school.school_id = teacher.teacher_id
GROUP BY school_name
UNION ALL
SELECT school_name,
'assistant',
AVG(assistant.salary)
FROM school
JOIN assistant ON school.school_id = assistant.school_id
GROUP BY school_name;
If not, please post the resultset you would like to see, in tabular format (use the [code="plain"][/code] for formatting), per the sample data you have already provided.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 10:50 am
thanks, opc.three, I'll use those tags in future.
regarding query, getting there. Now I would like to see each school's teachers AND assistants grouped together in 1. (a)(b) 2.(a)(b1)(b2) heirarchy format, as follows. Pivot?:
School1
teacher
assistant
School2
teacher
assistant
-
March 29, 2012 at 10:57 am
"Hierarchical format" as you put it is something of a display issue, better left to the presentation layer of an application. The resultset from my last post appears to deliver the data you want, now you just need to decide what you're going to use to display it. Reporting Services? An ASP.NET page?
Trying to handle these types of display issues within your SQL, for the sake of display in the SSMS Results Window is ill-spent time.
If you want to deliver the results as sorted, to relieve the presentation layer of that step, try adding an ORDER BY:
SELECT school_name,
'teacher' AS position,
AVG(teacher.salary) AS average_salary
FROM school
JOIN teacher ON school.school_id = teacher.teacher_id
GROUP BY school_name
UNION ALL
SELECT school_name,
'assistant',
AVG(assistant.salary)
FROM school
JOIN assistant ON school.school_id = assistant.school_id
GROUP BY school_name
ORDER BY school_name, POSITION DESC;
As far as adding "School Name" as a "master" header and displaying the results for that school (position and average_salary) underneath it, I recommend turning towards your presentation layer to achieve that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:06 am
opc.three. That is appreciated advice. My boss doesn't need that level of formatting at this point either, just verifying the data is there. I was going a step ahead but will leave of formatting until we decide on what we'll use to present.
I really appreciate the whole approach to feedback. Very thank you for UNION ALL.:kiss:
-
March 29, 2012 at 11:10 am
Anytime, and thank you for the positive feedback as well 😎
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply