CODEWITHSUNDEEP
sqlsql-server-2008
omkar patade
omkar patade

Reputation: 1591

SQL - subtract value from same column

I have a table as follows

fab_id  x  y  z  m  
12      14 10 3  5
12      10 10 3  4

Here im using group by clause on id .Now i want to subtract those column values which have similar id. e.g group by on id (12). Now to subtract (14-10)X, (10-10)Y, (3-3)z, (5-4)m

I know there is a aggregate function sum for addition but is there any function which i can use to subtract this value.

Or is there any other method to achieve the results.

Note- There may be a change that value may come in -ve. So any function handle this?

one more example - (order by correction_date desc so result will show recent correction first)

fab_id  x  y  z  m  correction_date
14      20 12 4  4   2014-05-05 09:03
14      24 12 4  3   2014-05-05 08:05
14      26 12 4  6   2014-05-05 07:12

so result to achieve group by on id (14). Now to subtract (26-20)X, (12-12)Y, (4-4)z, (6-4)m

Upvotes: 0

Views: 12298

Answers (7)

Johnny Fitz
Johnny Fitz

Reputation: 542

;with Ordered as 
(
select 
    fab_id,x,y,z,m,date, 
    row_Number() over (partition by fab_id order by date desc) as Latest,
    row_Number() over (partition by fab_id order by date) as Oldest
from fab
)
select 
   O1.fab_id, 
   O1.x-O2.x,
   O1.y-O2.y, 
   O1.z-O2.z, 
   O1.m-O2.m
from Ordered O1 
join Ordered O2 on 
O1.fab_id = O2.fab_id
where O1.latest = 1 and O2.oldest = 1

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Now, that you have given more information on how to deal with more records and that you revealed that there is a time column involved, here is a possible solution. The query selects the first and last record per fab_id and subtracts the values:

select 
  fab_info.fab_id,
  earliest_fab.x - latest_fab.x,
  earliest_fab.y - latest_fab.y,
  earliest_fab.z - latest_fab.z,
  earliest_fab.m - latest_fab.m
from 
(
  select 
    fab_id, 
    min(correction_date) as min_correction_date,
    max(correction_date) as max_correction_date
  from fab
  group by fab_id
) as fab_info
inner join fab as earliest_fab on 
  earliest_fab.fab_id = fab_info.fab_id and 
  earliest_fab.min_correction_date = fab_info.min_correction_date
inner join fab as latest_fab on 
  latest_fab.fab_id = fab_info.fab_id and 
  latest_fab.min_correction_date = fab_info.max_correction_date;

Upvotes: 2

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

Unfortunately, it's SQL Server 2012 that has the handy FIRST_VALUE()/LAST_VALUE() OLAP functions, so in the case of more than 2 rows we have to do something a little different:

SELECT fab_id, SUM(CASE WHEN latest = 1 THEN -x ELSE x END) AS x,
               SUM(CASE WHEN latest = 1 THEN -y ELSE y END) AS y,
               SUM(CASE WHEN latest = 1 THEN -z ELSE z END) AS z,
               SUM(CASE WHEN latest = 1 THEN -m ELSE m END) AS m

FROM (SELECT fab_id, x, y, z, m,
             ROW_NUMBER() OVER(PARTITION BY fab_id 
                               ORDER BY correction_date ASC) AS earliest,
             ROW_NUMBER() OVER(PARTITION BY fab_id 
                               ORDER BY correction_date DESC) AS latest
      FROM myTable) fab
WHERE earliest = 1
      OR latest = 1
GROUP BY fab_id
HAVING COUNT(*) >= 2

(and working fiddle. Thanks to @AK47 for the initial setup.)

Which yields the expected:

FAB_ID   X   Y   Z   M
12       4   0   0   1
14       6   0   0   2

Note that HAVING COUNT(*) >= 2 is so that only rows with changes are considered (you'd get some null result columns otherwise).

Upvotes: 1

Pred
Pred

Reputation: 9042

A CTE could help:

WITH cte AS (
    SELECT
        -- Get the row numbers per fab_id ordered by the correction date
        ROW_NUMBER() OVER (PARTITION BY fab_id ORDER BY correction_date ASC) AS rid
        , fab_id, x, y, z, m
    FROM
        YourTable
)
SELECT
    fab_id
    -- If the row number is 1 then, this is our base value
    -- If the row number is not 1 then, we want to subtract it (or add the negative value)
    , SUM(CASE WHEN rid = 1 THEN x ELSE x * -1 END) AS x
    , SUM(CASE WHEN rid = 1 THEN y ELSE y * -1 END) AS y
    , SUM(CASE WHEN rid = 1 THEN z ELSE z * -1 END) AS z
    , SUM(CASE WHEN rid = 1 THEN m ELSE m * -1 END) AS m
FROM
    cte
GROUP BY
    fab_id

Remember, 40-10-20 equals to 40 + (-10) + (-20)

Upvotes: -1

AK47
AK47

Reputation: 3807

I think if you have consistent set or two rows, then following code should work for you.

select fab_id ,max(x) - min(x) as x
        ,max(y) - min(y) as y
            ,max(z) - min(z) as z
            ,max(m) - main(m) as m
            from Mytable
group by fab_id

It will work, even if you get more than 2 rows in a group, but subtraction will be from max value of min value. hope it helps you.

EDIT : SQL Fiddle DEMO

Upvotes: -1

Allan S. Hansen
Allan S. Hansen

Reputation: 4091

Seeing as you say there will always be two rows, you can simply do a 'self join' and subtract the values from each other:

SELECT t1.fab_id, t1.x - t2.x as diffx, t1.y - t2.y as diffy, <remainder columns here>
from <table> t1
inner join <table> t2 on t1.fab_id = t2.fab_id and t1.correctiondate > t2.correctiondate

If you have more than two rows, then you'll need to make subqueries or use window ranking functions to figure out the largest and smallest correctiondate for each fab_id and then you can do the very same as above by joining those two subqueries together instead of

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Provided you always want to subtract the least value from the greatest value:

select 
  fab_id, 
  max(x) - min(x), 
  max(y) - min(y), 
  max(z) - min(z), 
  max(m) - min(m)
from fab
group by fab_id;

Upvotes: 1

Related Questions