SQL split row with calculation based on column value-CodePudding

I have a table with three columns :

Price | credit     | duration
------ ------------ ------------
1000  | 100        | 4 
2000  | 0          | 
1500  | 10         | 15

The goal is to split the rows based on the column duration value.

If duration is null --> then do nothing

If duration is not null --> then keep the line with the price add a new line where the price equals (price - credit*duration)

The result should look like follows :

Price | credit     | duration
------ ------------ ------------
1000  | 0          |
600   | 100        | 4 
2000  | 0          | 
1500  | 0          | 
1000  | 10         | 15

How can I do this using an SQL query ? I'm working on PostgreSQL as a Database.

CodePudding user response：

Sorry!!!

"price - credit * duration" you say but 600 - 100*4 == 1000 or 1000 - 15 *10 =1500 not equal .

 SELECT Price ,credit ,duration FROM table 
 UNION ALL
 SELECT Price - credit*duration, 0 , 0 FROM table

maybe this will help you sort it out if you want

CodePudding user response：

If I understand you correct, I think you want

select price ,credit ,duration --do nothing
from t 
where duration is null
union all
select price, 0 , null --keep the price, set credit to 0, duration to null)
from t
where duration is not null
union all
select price-(credit*duration), price , duration --add a new line with price =(price-credit*duration)
from t
where duration is not null