SQL & PL/SQL

I have an existing query that I wish to have 3 additional calculated columns added.

The existing query is:

SELECT t.swid as ID,
t.name,
t.swcatid,
t2.catdesc as Category,
t.liccnt,
(select count(swid) as swcount from swinv t2 where t2.swid=t.swid) Reccnt,
t.liccnt-(select count(swid) as swcount from swinv t2 where t2.swid=t.swid) as Avail,
t.last_update_date as updated
FROM SOFTWARE t
LEFT OUTER JOIN swcat t2 ON T.swcatid=t2.swcatid
order by t.name;

For the first calculated column, I need the sum of the liccnt where the SWIDs have the same GID in the following table:

TableName = colic
Columns: CID (primary key)
GID
SWID

I created a query that sums the liccnt in the Software table grouped by GID -- let's call this result (GroupLic):

SELECT t3.gid, nvl(Sum(t5.LicCnt),0) AS SumOfLicCnt
FROM CoLic t3 left outer JOIN Software t5 ON t3.SWID = t5.SWID
GROUP BY t3.gid;

The 2nd new calculated column should be the total of COUNTS of SWID in the SWInv where the SWID=the SWIDs in the CoLic table -- let's call this result (InstalledGroupLic):

TableName=SWInv
Columns: SID (primary key)
PCID
SWID
Last_Update_Date

I created a query that displays the counts by GID -- let's call this result (InstalledGroupLic)

SELECT t4.gid, (select count(t6.SID) AS CountofSWInv from swinv t6 where t6.swid=t4.swid) CollInstalled
FROM CoLic t4;

The 3rd Calculated column should be the difference between the SumOfLicCnt in GroupLic and CountofSWInv in InstalledGroupLic (SumOfLicCnt -CountofSWInv).

The columns should display a 0 if there is no record in the CoLic table with the same SWID in the Software table.

Any assistance with this would be greatly appreciated.

Thank you in advance.
Lillianne