CODEWITHSUNDEEP
mysqlsql
Ankit Agrawal
Ankit Agrawal

Reputation: 77

Mysql difference between rows

i want to get price difference of car from 2 row through given following data. i want to substract price column ex: (200-100),(300-200) and so on as data

My Table:

enter image description here

My desired output:

what i have tried 

select t1.row_num1,t1.car_name
from
(
    select (@row_num := @row_num +1) as row_num1 ,(select @row_num =0) r1, car_name,price  
    from car
)t1

I know that i don't have id column.hence i am generating row_number. now i am getting problem to self join this table and get difference. your help is appreciable.

Upvotes: 0

Views: 122

Answers (3)

1000111
1000111

Reputation: 13519

Although your output seems confusing nevertheless I am giving the following answer:

SOLUTION #1

SELECT 
carsTable1.car_name,
carsTable1.price,
CASE WHEN ABS(carsTable1.price - (SELECT price FROM cars WHERE car_name='car 2')) = 0 THEN NULL ELSE 
        ABS(carsTable1.price - (SELECT price FROM cars WHERE car_name='car 2')) END diff
FROM
(SELECT 
    @rn := @rn + 1 row_number,
    cars.car_name,
    cars.price 
FROM cars, (SELECT @rn := 0) var
) carsTable1;

Demo Here

Sample Input:

car_name   price 

car 1       100

car 2       200

car 3       300

Sample Output:

    car_name   price     diff

    car 1       100      100

    car 2       200      NULL

    car 3       300      100

Note: The price of car 2 is compared with the price of the rest of the cars. So the result shows null for car 2 since it's the reference car.

If I misunderstood your requirement then it must be : You want the price differences between the consecutive rows i.e. (No car,car1),(car1,car2), (car2,car3), (car3,car4)....

So in this case you can adopt the following query :

SOLUTION #2

SELECT 
car_name,
cars.price,
CASE WHEN @currentPrice = 0 THEN NULL ELSE ABS(cars.price - @currentPrice) END AS diff,
@currentPrice := price
FROM cars ,(SELECT @currentPrice := 0) var
ORDER BY car_name

SQL FIDDLE BASED ON THIS QUERY

And if you want to omit the fourth column:

SELECT 
t.car_name,
t.price,
t.diff
FROM 
(
    SELECT 
    car_name,
    cars.price,
    CASE WHEN @currentPrice = 0 THEN NULL ELSE (cars.price - @currentPrice) END AS diff,
    @currentPrice := price
    FROM cars ,(SELECT @currentPrice := 0) var
    ORDER BY car_name ) t

SQL FIDDLE BASED ON THIS QUERY

Upvotes: 1

Amee
Amee

Reputation: 347

Try this:- 

CREATE TABLE #TempTable (rownum INT, price int, car_name VARCHAR(256));
INSERT INTO #TempTable (rownum, price, car_name)
SELECT
 rownum = ROW_NUMBER() OVER (ORDER BY c.car_id),
 c.price,
 c.car_name
 FROM car c;
 SELECT
  NEX.car_name + '-' + TT.car_name,
  (nex.price - tt.price) AS Differences
  FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

set @next_row_price := null;
SELECT car_name , price, diff FROM(
  SELECT car_name,price,(@next_row_price - price) * -1 AS diff, 
  IF(@next_row_price IS NULL, @next_row_price := price, 0) ,
  IF(@next_row_price IS NOT NULL, @next_row_price := price, 0) 
FROM car 
) AS TEMP;

SQLFiddle

Upvotes: 1

Related Questions