select s.partNo, p.descript, sum(case when battState = '99' then qty else '0'end) as CLNT, sum(case when battState = '1' then qty else '0' end) as AVAIL, sum(case when battState = '20' then qty else '0' end) as QAH, sum(case when battState = '30' then qty else '0' end) as BOOST, sum(case when battState = '31' then qty else '0' end) as RESTING from batt_State s, pnLU as p where 0=0 and s.PartNo = p.PartNo group by s.partNo, p.descript

select s.partNo, p.descript, sum(case when battState = '99' then qty else '0'end) as CLNT, sum(case when battState = '1' then qty-newQty else '0' end) as AVAIL, sum(case when battState = '20' then qty-newQty else '0' end) as QAH, sum(case when battState = '30' then qty-newQty else '0' end) as BOOST, sum(case when battState = '31' then qty-newQty else '0' end) as RESTING from batt_State s, pnLU as p where 0=0 and s.PartNo = p.PartNo group by s.partNo, p.descript

from batt_State sjoin pnLU as p on s.PartNo = p.PartNogroup by s.partNo, p.descript

SELECT s.partNo, p.descript, CLNT = SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END), AVAIL = SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END), QAH = SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END), BOOST = SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END), RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END) FROM batt_State s INNER JOIN pnLU p ON s.PartNo = p.PartNo WHERE battState IN ('1','20','30','31','99') GROUP BY s.partNo, p.descript