SQL Help (For Each)

Question

I'm trying to write an SQL Statement that needs to loop through a list of departments, kind of like a For Each.

Heres what I have so far.

SELECT DISTINCT AVG(salary),assigned->name
FROM edu_ucla_cs241_termproj_schema.InstructorImpl
WHERE assigned->name = 'Anthropology'

This will give me a table of Avg Salary Department 90.15 Anthropology

However, I want to loop through each department. Can I make this query take a variable and loop through each department name?

Answer 1

Can you not use

SELECT  AVG(salary),
     assigned->name
FROM edu_ucla_cs241_termproj_schema.InstructorImpl
GROUP BY assigned->name

Answer 2

SELECT  assigned->name, AVG(salary)
FROM edu_ucla_cs241_termproj_schema.InstructorImpl
GROUP BY assigned->name

You can also get a bit more info as in

SELECT  assigned->name, COUNT(*) AS NbEmployees, AVG(salary) AS AvSalary, 
        MIN(salary) AS MinSalary, MAX(salary) AS MaxSalary
FROM edu_ucla_cs241_termproj_schema.InstructorImpl
GROUP BY assigned->name

Your request/suggestion for a "variable [to loop through departments]" indicates your thinking in terms of procedural (imperative) programming languages. While SQL, or SQL extensions support this programing paradigm, you'll find that SQL is a lot more powerful when used in a declarative fashion (as with the queries above: you "declare" what you want, and let SQL worry about doing the "looping" and other steps necessary to fulfill the request)

Answer 3

If you want the average salary for all records AND a listing of the name column for each record, you need to use 2 separate queries

Answer 4

Looping through query results is done in most databases through the use of cursors inside a stored procedure. Without knowing the specifics of the question and the database in use, it's tough to advise how you might implement what you want.

Answer 5

If you would like to get the avg salary for all assigned->name [ i hope this is department name ], then try the solution given by astander

SELECT assigned->name,AVG(salary)
FROM edu_ucla_cs241_termproj_schema.InstructorImpl    
GROUP BY assigned->name

otherwise, if you wantlto get the avg salary for certain departments then you will have to create the query dynamically, like this:

SELECT assigned->name,AVG(salary)
FROM edu_ucla_cs241_termproj_schema.InstructorImpl
WHERE assigned->name in ( 'name1', 'name2' ... )
GROUP BY assigned->name