Sum 2 column from different rows-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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