SQL server query joins

Question

Goal - List average salary for men and average salary for women for each department. Show department name and number.

 select AVG(salary) as 'Avg salary for men' 
   From [Enterprise].[dbo].[Employee]JOIN [Enterprise].[dbo].[Department]
   ON Employee.EMPNO = Department.deptNo
   where SEX = 'm'

Still learning joins not exactly sure if i need an outer join here? And how do i get it to list avg by department?

`Employee.EMPNO = Department.deptNo`?, this doesn't seem right, can you list the columns of the `Employee` table? — Lamak, Nov 04 '13 at 21:06

score 1 · Answer 1 · answered Nov 04 '13 at 21:11

I'm going to assume you meant to join by deptNo.

SELECT Department.Name, AVG(salary) as 'Avg salary for men' 
FROM [Enterprise].[dbo].[Employee]
JOIN [Enterprise].[dbo].[Department]
ON Employee.deptNo= Department.deptNo
WHERE SEX = 'm'
GROUP BY Department.Name

This will show the average for each department for males. You can do the same for females, and include them in the query with UNION. Or add to your GROUP BY sex, and remove the WHERE clause entirely.

The list for GROUP BY usually will match your non aggregated columns in the SELECT list. In this case it is just Department.Name.

If you use the UNION approach, use UNION ALL as these two sets of records shoudl be mutually exclusive unless your system allows you to select more than one sex per person. UNOIN ALL is significantly faster. — HLGEM, Nov 04 '13 at 22:09

score 1 · Answer 2 · answered Nov 04 '13 at 21:11

Well, first of all, it seems that you are using the wrong column from Employee on the JOIN. That said, your query should be like this:

SELECT  D.deptNo,
        D.deptName,
        AVG(CASE WHEN E.SEX = 'm' THEN salary END) [Avg salary for men],
        AVG(CASE WHEN E.SEX = 'f' THEN salary END) [Avg salary for women]
FROM [Enterprise].[dbo].[Employee] E
INNER JOIN [Enterprise].[dbo].[Department] D
    ON E.deptNO = D.deptNo     -- use the right column from Employee here
GROUP BY D.deptNo,
         D.deptName

score 0 · Answer 3 · answered Nov 04 '13 at 21:11

SELECT Employee.EMPNO, Department.Name, SEX, AVG(salary) as [Avg salary] 
FROM [Enterprise].[dbo].[Employee]JOIN [Enterprise].[dbo].[Department]
   ON Employee.EMPNO = Department.deptNo
GROUP BY Employee.EMPNO, Department.Name, SEX

This will get you the data, from here you can format it as you please.

score 0 · Answer 4 · edited May 23 '17 at 12:21

The SQL OUTER JOIN clause is a variation of the SQL JOIN clause enables a SELECT statement to access more than one table. The JOIN clause controls how tables are linked. It is a qualifier of the SQL FROM clause. As result you need the OUTER JOIN in your case. You can visit the link for further information if you want.

To get an output as a list avg by Department, just add this to the end of your query:

ORDER BY Department.deptNo

score 0 · Answer 5 · answered Nov 04 '13 at 22:17

No one has accounted for the fact that some departments might contain no men or no women.

SELECT  D.deptNo,
        D.deptName,
        Coalesce(AVG(CASE WHEN E.SEX = 'm' THEN salary END), 0) [Avg salary for men],
        Coalesce(AVG(CASE WHEN E.SEX = 'f' THEN salary END, 0)) [Avg salary for women]
FROM [Enterprise].[dbo].[Department] D
LEFT JOIN [Enterprise].[dbo].[Employee] E
    ON E.deptNO = D.deptNo     -- use the right column from Employee here
GROUP BY D.deptNo,
         D.deptName

Chris Cameron-Mills · Accepted Answer · 2013-11-06T08:59:58.650

As pointed out, your join condition doesn't make sense, you are joining an employee number to a department number, which wouldn't match (or would, but wouldn't achieve the logic you desire).

SELECT
  d.name
, AVG(e_man.salary) avg_man
, AVG(e_woman.salary) avg_woman
FROM department d
LEFT OUTER JOIN employee e_man ON e_man.deptNo = d.deptNo AND e_man.sex = 'm'
LEFT OUTER JOIN employee e_woman ON e_woman.deptNo = d.deptNo AND e_woman.sex = 'f'
GROUP BY d.name

Here you join twice, once to male rows and once to female and calculate the averages based on them. OUTER joins are used in case there aren't any men/women.

Note: my queries will return you a single row per department with male and female averages as 2 columns. Alternate solutions are to return you 0-2 rows per department, with separate rows per sex and listing the relevant salary per row/sex.

EDIT: Alternative sub-query solution which people seem very unhappy with. In my experience sometimes sub-queries are the right answer and sometimes a join is the right answer, and you would often use whichever fits your logical aim/representation best as long as it doesn't sacrifice performance. The optimiser for the RDBMS you are using will often rewrite it anyway. As always, best to check the Plan and see what is going on.

SELECT
  d.name
, (
    SELECT AVG(e1.salary)
    FROM employee e1
    WHERE e1.sex = 'm'
    AND e1.deptNo = d.deptNo
  ) avg_man
, (
    SELECT AVG(e2.salary)
    FROM employee e2
    WHERE e2.sex = 'f'
    AND e2.deptNo = d.deptNo
  ) avg_woman
FROM department d

That is correct and there is an option to do it via OUTER JOINs — LINQ2Vodka, Nov 04 '13 at 21:11
-1 - Sub-query in the `SELECT` shouldn't be the first option. — Yosi Dahari, Nov 04 '13 at 21:13
SUbselect is the last option I would ever use. For anything. Subselects are like cursors in how they operate. The join query is fine, but don't suggest subselects. — HLGEM, Nov 04 '13 at 22:07

score -1 · Answer 7 · answered Feb 24 '14 at 04:28

Query1 :

SELECT Table1.*, Table2.[Roll no],Table2.Address,Table2.City
FROM (Table1 right JOIN Table2 ON Table1.[roll no] = Table2.[roll no] and Table1.[status] = Table2.[status]) 
where Table1.Status="New" or Table2.status="New"
UNION SELECT Table1.*,Table2.[roll no], table2.Address,table2.City
FROM (Table1 left JOIN Table2 ON Table1.[roll no] = Table2.[roll no] and Table1.[status] = Table2.[status])
where Table1.Status="New" or Table2.status="New";


Qury2:

SELECT Table3.*,table4.[roll no],Table4.Sports_id,Table4.Name
FROM (Table3 right JOIN Table4 ON Table3.[roll no] = Table4.[roll no] and Table3.[status] = Table4.[status]) 
where Table3.Status="New" or Table4.status="New"
UNION SELECT Table3.*,table4.[roll no],Table4.Sports_id,Table4.Name
FROM (Table3 left JOIN Table4 ON Table3.[roll no] = Table4.[roll no] and Table3.[status] = Table4.[status])
where Table3.Status="New" or Table4.status="New";


Query3:

SELECT * 
FROM (Query2 right JOIN Query3  ON Query2.[Table1.roll no] = Query3.[table3.roll no]) 
UNION SELECT *
FROM (Query2 left JOIN Query3  ON Query2.[Table1.roll no] = Query3.[table3.roll no]);


Query4:

SELECT Query4.* INTO Final_table
FROM Query4;


Query5:

UPDATE Final_Table SET Query2_Status = "New"
WHERE Query2_Status is null or Query2_status="";

SQL server query joins

7 Answers7