Find difference b/w avg salary of departments

You can do this using CASE to get the rows you want:

DDL

create table t (
  Dept char(2),
  sal int
  )

  insert t (dept, sal)
  values('aa', 1234),
  ('aa', 1234),
  ('bb', 5432),
  ('bb', 4321)

Answer

select avg(case when dept="aa" then sal else null end) - avg(case when dept="bb" then sal else null end)
from t

This will only require a single scan of your table, so should be faster than the subquery route.

Browse More Popular Posts

Leave a Comment