Hello,
I'm looking to show the avg company total and then in another column the average department total. I would then like to have a case statement to show if the department is higher than the company average. And then group by department.
Added a screenshot of some how the tables would look.
Welcome! In the future, you will get the best responses if you provide sample data in a usable form, as shown below, rather than in a picture. That way responders can immediately start writing code.
CREATE TABLE #Test ( row int NOT NULL, id int NOT NULL,
name varchar(30) NOT NULL );
INSERT INTO #Test VALUES
(1, 1, 'IT'), (2, 2, 'Sales'), (3, 3, 'Customer');
CREATE TABLE #Test_Two ( row int NOT NULL, id int NOT NULL,
name varchar(30) NOT NULL, salary int NOT NULL, TestId int NOT NULL );
INSERT INTO #Test_Two VALUES
(1, 1, 'James', 750, 1),
(2, 2, 'Jim', 260, 2),
(3, 3, 'John', 400, 2),
(4, 4, 'Jake', 500, 2),
(5, 5, 'Hope', 800, 1),
(6, 6, 'Stu', 600, 3);
;WITH cte_get_company_avg AS (
SELECT AVG(salary * 1.0) AS Avg_Company_Salary
FROM #Test_Two
)
SELECT T.Name, AVG(TT.salary * 1.0) AS Avg_Dept_Salary,
CASE WHEN AVG(TT.salary * 1.0) > MAX(cte.Avg_Company_Salary) THEN 'Avg Dept Salary is higher than Company Avg' ELSE '' END
FROM #Test_Two TT
INNER JOIN #Test T ON T.id = TT.TestId
CROSS JOIN cte_get_company_avg cte
GROUP BY T.Name