vendredi 25 septembre 2015

Order by sum from another table

I have these 2 tables:

School:

"CREATE TABLE IF NOT EXISTS schoool (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name CHAR NOT NULL" +
                ");"

Teacher:

"CREATE TABLE IF NOT EXISTS teacher ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "school_id NOT NULL, " +
                "name CHAT NOT NULL, " +
                "wage REAL NOT NULL," +
                "CONSTRAINT fk_school_id FOREIGN KEY (school_id) " +
                "REFERENCES school (id) ON DELETE CASCADE" +
                ");"

I need to select all schools ordering by the total amount salary is expend on the teacher staff.

Example: Teachers

id, school_id, name, wage
1, 1, 'teacher1', 2000
2, 1, 'teacher2', 1500
3, 2, 'teacher3', 5600
4, 3, 'teacher4', 4300
5, 4, 'teacher5', 5000
6, 4, 'teacher6', 3500
7, 4, 'teacher7', 4000

This should be the expected result:

school.id, school.name
4, 'school 4'
2, 'school 2'
3, 'school 3'
1, 'school 1'

Aucun commentaire:

Enregistrer un commentaire