I have 2 tables, Tbl1 and Tbl2 :

Tbl1:

ID  Col1    Col2    Sold    Total
1    AA               0      100
1    BB     CC        2      200
1    DD     EE        3      300
2    FF     GG        1      100

Tbl2:

ID   Sold   Total    TotalPerPax
      

I need to calculate the TotalPerPax in Tbl2 depending on the ID But the calculation of the TotalPerPax is like this. Example:

ID = 1
Sold: 0 + 2 + 3 = 5
Total = 100 + 200 + 300 = 600
TotalPerPax = (Total  minus the Total of the row that has 0 sold  / Sold )
              (600 -100 ) / 5 =   500

The output should look like this
Tbl2:

ID  Sold    Total    TotalPerPax
1    5       600       100  -- (500 Total / 5 Sold)
2    1       100       100

So far I have this:
When executing it throws an error "Divide by zero error encountered" thus I can’t compute the totalPerPax correctly. Can anyone can help me to with this? Thanks

SELECT ID,
    Col1
    Col2,
    Sold,
    Total,
    SUM(COALESCE(Total, 0))/SUM(COALESCE(Sold, 0)) As TotalPerPax
FROM Tbl1 t1
Where ID = 1
GROUP BY ID, Col1, Col2,Sold, Total

Sample sql fiddle: http://sqlfiddle.com/#!18/09971/2

>Solution :

aI would phrase this as:

SELECT
    ID,
    SUM(Sold) AS Sold,
    SUM(Total) AS Total,
    CASE WHEN SUM(Sold) > 0
         THEN SUM(CASE WHEN Sold > 0 THEN Total ELSE 0 END) /
              SUM(CASE WHEN Sold > 0 THEN Sold ELSE 0 END)
         ELSE 0 END AS TotalPerPax
FROM TBl1
GROUP BY ID;

The CASE expression for TotalPerPax uses logic which does not include any total or sold amount when the latter happens to be zero. As a note, for any ID which only might have zero sold amounts, TotalPerPax would be reported as zero.