CODEWITHSUNDEEP
sqloracle-database
pratteek shaurya
pratteek shaurya

Reputation: 960

sql query to get output in ascending order

I have a table for with following columns : Salary and Department

and these are contents of table:

IT      600000

HR      530000

IT     1000000

HR      480000

Payroll 500000

I wanted to write a query to get the department, total sum of salaries for each department and order them in the ascending order of the total sum of salaries.

This is the query which I tried:

select Department, salary,
sum(Salary)
from table 
order by Department DESC;

But I am getting output as:

Payroll 500000 3110000

I need to get output where the sum of each department is shown.

Upvotes: 0

Views: 176

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Then use group by Department

select Department, sum(Salary) as salary
  from "table" 
 group by Department
 order by salary
  • table is a reserved keyword for oracle, so I wrapped within quotes
  • non-aggregated salary column should be removed from the select-list

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Here's how:

SQL> with test (department, salary) as
  2    (select 'IT'     ,  600000 from dual union all
  3     select 'HR'     ,  530000 from dual union all
  4     select 'IT'     , 1000000 from dual union all
  5     select 'HR'     ,  480000 from dual union all
  6     select 'Payroll',  500000 from dual
  7    )
  8  select department, sum(salary) sum_sal
  9  from test
 10  group by department
 11  order by sum_sal;

DEPARTM    SUM_SAL
------- ----------
Payroll     500000
HR         1010000
IT         1600000

SQL>

Your query contains salary column which is wrong in this context, as you have to group by salary as well, and you'll get the source table itself.

Upvotes: 1

Related Questions