CODEWITHSUNDEEP
mysqldatabase
VijayIndia
VijayIndia

Reputation: 91

Using Case statement in select

I have a table 

id     salary

1            100

2            200

3            300

4            400

Now I wanted to find the average salary and then compare with each individual salary and then say as 'High' or 'Low'.

Whether this can be done with case statement or please suggest me some good way?

My query is 

select xxxxxxxxxx = 
case 
when(salary>(select avg(salary)from table_name))
then 'High'
when(salary<(select avg(salary)from table_name))
then 'low' 
end;

But here my problem is i dont have that xxxxxxxxx column in my table.

is there anyway i can get the value -'High' or 'Low' without adding/Specifying a column in that table to store values?

So please suggest me a solution with using case statement.

Upvotes: 0

Views: 87

Answers (3)

brenners1302
brenners1302

Reputation: 1478

Try this:

SELECT A.employee_id, 
       A.salary, 
      (CASE WHEN b.ave > A.salary THEN 'LOW' 
            WHEN b.ave=A.salary THEN 'AVERAGE' 
            ELSE 'HIGH' END) remarks 
FROM oehr_employees A,
     (select avg(salary) ave from oehr_employees) b;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use a join:

select tn.*,
       (case when tn.salary > a.avgs then 'High'
             when tn.salary < a.avgs then 'low'
        end)
from table_name tn cross join
     (select avg(salary) as avgs from tn) a;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

There are a couple of ways to do this. Here's one option:

select id, 
    case when salary > avgsalary then 'High' else 'Low' end 
from yourtable y
   join (select avg(salary) avgsalary from yourtable) y2

Upvotes: 1

Related Questions