Find difference b/w avg salary of departments

by

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.

More Related Contents:

  1. Case statement for multiple columns
  2. Transaction lock in sql
  3. SQLite – UPSERT *not* INSERT or REPLACE
  4. Unioning two tables with different number of columns
  5. Save PL/pgSQL output from PostgreSQL to a CSV file
  6. ORA-00904: invalid identifier
  7. How do I use variables in Oracle SQL Developer?
  8. Fastest way to count exact number of rows in a very large table?
  9. Formatting Numbers by padding with leading zeros in SQL Server
  10. Is SQL IN bad for performance?
  11. Importance of varchar length in MySQL table
  12. What are your most common sql optimizations?
  13. Postgres Case Sensitivity
  14. SQL to find the number of distinct values in a column
  15. How do I query for all dates greater than a certain date in SQL Server?
  16. Drop all tables command
  17. Sql script to find invalid email addresses
  18. can we list all tables in msaccess database using sql?
  19. What does the colon sign “:” do in a SQL query?
  20. Syntax of for-loop in SQL Server
  21. Using alias in query and using it
  22. Why does ISNUMERIC(‘.’) return 1?
  23. Comparison of Relational Databases and Graph Databases
  24. The difference between ‘AND’ and ‘&&’ in SQL
  25. Group consecutive rows of same value using time spans
  26. Linq to SQL: How do I stop the auto generated object name from being renamed?
  27. Using django how can I combine two queries from separate models into one query?
  28. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  29. Previous Monday & previous Sunday’s date based on today’s date
  30. SQL add filter only if a variable is not null

Leave a Comment