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.