Statistics after the unified unit of Oracle group by and case when
Table data
Select * from ware_info2
1 sulfuric acid 1.0000 kg
2 hydrochloric acid 2.0000 kg
3 3.0000 tons of potassium permanganate
4 potassium permanganate 4.0000 kg
5 potassium permanganate 5000.0000 ml
6 6.0000 litres of potassium permanganate
7 7.0000 litres of potassium permanganate
Convert milliliters to liters, tons to kilograms, aggregate data
Select name,sum (
Case
When unit=' ton 'then qty*1000
When unit=' ml 'then qty/1000
Else qty end)
Case
When unit=' ton 'then' kg
When unit=' milliliter 'then' liter
When unit=' rise 'then' rise
When unit=' kg 'then' kg
End as unit
From ware_info2
Group by name,case
When unit=' ton 'then' kg
When unit=' milliliter 'then' liter
When unit=' rise 'then' rise
When unit=' kg 'then' kg
End
Results:
Potassium permanganate 3004 kg
1 kg sulfuric acid
2 kg hydrochloric acid
18 litres of potassium permanganate
