CODEWITHSUNDEEP
sqlsql-server
Kart28
Kart28

Reputation: 13

Sum 2 column from different rows

I extract data from my table by use below query.

SELECT  ID ,Desc_Cars ,DocID ,TabID
    ,(Select Dist1 where TabID = 85)
    ,(Select Dist2 where TabID = 86) 
    ,(Select Days1 where TabID = 85)    
    ,(Select Days2 where TabID = 85)
    ,(Select Days3 where TabID = 86)    
FROM Details
      where   DocID = 16

I have following part of table in SQL:

ID Desc_Cars DocID TabID Dist1 Dist2 Days1 Days2 Days3
607 Car1 16 85 481 NULL 11 0 NULL
608 Car2 16 85 2072 NULL 21 2 NULL
609 Car3 16 85 333 NULL 15 6 NULL
610 Car4 16 85 1564 NULL 14 0 NULL
611 Car1 16 86 NULL 118 NULL NULL 4
612 Car2 16 86 NULL 12 NULL NULL 0
613 Car3 16 86 NULL 133 NULL NULL 10
614 Car4 16 86 NULL 777 NULL NULL 17

How can I SUM columns Dist1+Dist2 and Days1+Days2+Days3 to get that result

Desc_Cars Sum_Dist Sum_Days
Car1 599 15
Car2 2084 23
Car3 555 31
Car4 2341 31

I always operate on 1 DocID. Each DocID has always 2 tables: TabID 85 and 86

............................

Hi, the reason why I use Select in Select was that TabID 85 & 86 has values in other columns too

SELECT  ID ,Desc_Cars ,DocID ,TabID
        ,Dist1
        ,Dist2
        ,Days1
        ,Days2
        ,Days3
    FROM Details
          where   DocID = 16
ID Desc_Cars DocID TabID Dist1 Dist2 Days1 Days2 Days3
607 Car1 16 85 481 NULL 11 0 NULL
608 Car2 16 85 2072 NULL 21 2 NULL
609 Car3 16 85 333 NULL 15 6 NULL
610 Car4 16 85 1564 NULL 14 0 NULL
611 Car1 16 86 2129 118 10 2101 4
612 Car2 16 86 612 12 2 601 0
613 Car3 16 86 52 133 2 55 10
614 Car4 16 86 59 777 3 800 17

https://dbfiddle.uk/sAH7sv89

expected result, sum values like on picture: enter image description here

Upvotes: 1

Views: 102

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Probably easiest to do with a self join:

select d1.Desc_Cars, 
       SUM(COALESCE(d1.Dist1, 0) + COALESCE(d2.Dist2, 0)) Sum_Dist,
       SUM(COALESCE(d1.Days1, 0) + COALESCE(d1.Days2, 0) + COALESCE(d2.Days3, 0)) Sum_Days
from Details d1
join details d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.tabid = d2.tabid-1
  and d1.docid = d2.docid
where   d1.DocID = 16  
group by d1.Desc_Cars

Fiddle

@Jarlh:s version, letting the aggregate deal with nulls is more elegant in my opinion

select d1.Desc_Cars, 
       SUM(d1.Dist1) + SUM(d2.Dist2) Sum_Dist,
       SUM(d1.Days1) + SUM(d1.Days2) + SUM(d2.Days3) Sum_Days
from Details d1
join details d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.tabid = d2.tabid-1
  and d1.docid = d2.docid
where   d1.DocID = 16  
group by d1.Desc_Cars

If tabid is not guaranteed to be consecutive, you can use row_number to match with next one:

with t (Desc_Cars, DocID, Dist1, Dist2, Days1, Days2, Days3, rn) AS (
  select Desc_Cars, DocID, Dist1, Dist2, Days1, Days2, Days3
       , row_number() over (partition by Desc_Cars, DocID order by tabid) as rn
  from Details
)
select d1.Desc_Cars, 
       SUM(d1.Dist1) + SUM(d2.Dist2) Sum_Dist,
       SUM(d1.Days1) + SUM(d1.Days2) + SUM(d2.Days3) Sum_Days 
from t d1
join t d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.rn = d2.rn-1
  and d1.docid = d2.docid
group by d1.Desc_Cars;

Upvotes: 2

jarlh
jarlh

Reputation: 44766

Do a GROUP BY:

select Desc_Cars,
       SUM(Dist1) + SUM(Dist2) Sum_Dist,
       SUM(Days1) + SUM(Days2) + SUM(Days3) Sum_Days
from Details
where DocID = 16   --  <-- perhaps this condition is needed? 
group by Desc_Cars

Upvotes: 2

Related Questions