Select statement with multiple rows from condition on values in single column

Question

I have the table below.Using salary as condition I want to get multiple rows. Below is current table call it employee.

    empid     name         salary
-----------------------------------
1   A1        alex        20000
2   B2        ben         4500
3   C1        carl        14000

compare the salary to certain fixed values, and every time the salary is larger than the fixed value, show a record in output.My attempt condition case is close to this:

   incometype= case When salary<6000 then 101 When salary Between 6000 And 18000 Then
   102 Else 103 End

Desired ouput would be:

     empid     name   salary  incometype
------------------------------------------
1   A1        alex    20000    101
2   A1        alex    20000    102
3   A!        alex    20000    103
4   B2        ben     4500     101
5   C1        carl    14000    101
6   C1        carl    14000    102

I have tried using union but union will give me 3 rows for each record even when value meets 1st condition.

Shouldn't that be 1 to 1 mapped to your input? Why do you want to return 6 rows for 3 input rows? — shree.pat18
– shree.pat18, Commented Jun 19, 2014 at 6:51
@shree.pat18 I need them like that for taxtion bracket range.Long story short my ideal output needs to be as above — GotaloveCode
– GotaloveCode, Commented Jun 19, 2014 at 7:03

shree.pat18 · Accepted Answer · 2014-06-19 08:17:39Z

4

Your question is unclear, because your logic implies that you should only have 3 output rows for 3 input rows. Your output however implies that you want to compare the salary to certain fixed values, and every time the salary is larger than the fixed value, show a record in output.

If the former is the case, Minh's query is all you need. In the latter case, you can do something like this:

select e.*, m.incometype
from employee e
left join 
( 
 select 0 as threshold, 101 as incometype
 union
 select 5999 as threshold, 102 as incometype
 union
 select 17999 as threshold, 103 as incometype
) m
on e.salary > m.threshold
order by e.empid

If you want to add a calculate column i.e. one with values calculated using columns in this query, you can simply add it as a column in the select clause, like so:

select e.*, 
m.incometype, 
case 
when <first condition> then <business logic here>
....
else <handle default case>
end as yourcomputedcolumn
from
...

edited Jun 19, 2014 at 8:17

answered Jun 19, 2014 at 7:02

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

GotaloveCode Over a year ago

I offered that condition for explanation purpose as I could not establish proper logic your solution above might do the trick.My query is much larger and complex so I tuned down to the aspect that I can get quick assistance without overload.Thanks

shree.pat18 Over a year ago

Fair enough. Let me know if my solution works for you. BTW which RDBMS are you using, although mine uses standard operations only.

GotaloveCode Over a year ago

what if i need to add another column tax still based on grosspay where for type 101 tax=0.6 *grosspay,102 tax=0.6 *(grosspay-6000),103 tax =0

shree.pat18 Over a year ago

I suggest you add this as a separate question for better visibility. In a nutshell however, once you have the columns, you can just add a calculated column to the query.

GotaloveCode Over a year ago

can you initiate chat am sure its something minor I just dont know how to add it in the query

|

MinhD · Accepted Answer · 2014-06-19 06:53:43Z

1

This returns 3 rows and enough for your need:

  SELECT empid, name, salary, 
    case When salary<6000 then 101 
         When salary Between 6000 And 18000 Then 102 
         Else 103 End as incometype
    FROM employee;

answered Jun 19, 2014 at 6:53

MinhD

1,84812 silver badges14 bronze badges

2 Comments

GotaloveCode Over a year ago

that will give me just a single output not ideal for my need.For example 14000 will not appear in two rows one with 101 another 102

GotaloveCode Over a year ago

well your right according to above query but ideally it should be taxed on 101 and 102.

Dmitrii Erokhin · Accepted Answer · 2014-06-19 07:35:59Z

1

Not very clear on the requirement, however the following worked for me:

Select 
    EmpId,Name,Sal,101 IncomeType
from Emp
Union all
Select 
    EmpId,Name,Sal,102
from Emp
Where Sal > 6000
union all
Select 
    EmpId,Name,Sal,103
from Emp
Where Sal > 18000;

edited Jun 19, 2014 at 7:35

Dmitrii Erokhin

1,35513 silver badges32 bronze badges

answered Jun 19, 2014 at 7:12

anonxen

8241 gold badge11 silver badges24 bronze badges

4 Comments

GotaloveCode Over a year ago

that was my initial trial however that will give 3 rows every tym see @shree's solution.Thats what I needed.thanks though.

anonxen Over a year ago

I don't think so.Please note that I have filtered the records based on a different set of conditions. Only the first select returns all the rows from the table.

GotaloveCode Over a year ago

I hadn't noticed that actually this looks like it should work too.let me try it plus I need to addd another column tax where if salary <6000 tax=0.06*salary,else if salary between 6000 and 18001 then tax=0.06*(salary-6000) else tax=0

anonxen Over a year ago

This should do it: Select EmpId,Name,Sal,IncomeType,Case IncomeType When 101 then 0.6 * Sal WHEN 102 then 0.6 *(Sal-6000) Else 0 End as Tax From (Select EmpId,Name,Sal,101 IncomeType from Emp Union all Select EmpId,Name,Sal,102 from Emp Where Sal > 6000 union all Select EmpId,Name,Sal,103 from Emp Where Sal > 18000);

Collectives™ on Stack Overflow

Select statement with multiple rows from condition on values in single column

3 Answers 3

7 Comments

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related