Hitung stok keluar, masuk, rusak, total stok

mohon dibantu senior, menghitung Stok keluar, masuk, rusak, total stok. dibawah ini kode yang saya pake

SELECT asset.sn, asset.nm_asset, asset_type.type_asset,
(SELECT COUNT(*) FROM asset) as Total,
(SELECT COUNT(*) FROM asset WHERE asset_assign.sn=asset.sn AND asset_status='A' AND cond='Complete') as Aktif,
(SELECT COUNT(*) FROM asset_assign WHERE asset_assign.sn=asset.sn AND asset_status='NA' AND cond='Missing') as Missing,
(SELECT COUNT(*) FROM asset_assign WHERE asset_assign.sn=asset.sn AND asset_status='NA' AND cond='Damaged') as Damaged
FROM asset
JOIN asset_assign
ON asset_assign.sn=asset.sn
JOIN asset_type
ON (asset_type.id_type_asset=asset.id_type_asset)

ini gambar table dan hasilnya : https://drive.google.com/open?id=1GnDC1dOE8wlAP6gbkCgpyyZUfPFmGdTF