# Calculate Total per pax excluding the row that has 0 in the column

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;
``````

## Demo

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.