I have a SQL report that counts the number of instances of a field. I am pulling this into a summary report. This works using the Lookupset function. However, I want to take the total of this column and find the percentage of the rows. For example:
Preferred Employer = Yes, Preferred Employer = No I want to be able to add a Percentage Column next to the count column that would show the Percentage of each row. The end result would total 100
Count formula: =Join(LookupSet(Fields!pscode.Value, Fields!pscode.Value, Fields!PrefCnt.Value, "PrefEmployer"), vbCrLF + vbCrLF)
Total: =RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer")
Percent:
=IIF(RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer") <> 0, Join(LookupSet(Fields!pscode.Value, Fields!pscode.Value, Fields!PrefCnt.Value, "PrefEmployer"), vbCrLF + vbCrLF)/RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer")*100,0)
SELECT
vt.pscode as pscode,
vt.PrefEmpl as PrefEmpl,
SUM(vt.PrefCnt) as PrefCnt
FROM
(
SELECT
rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')' pscode,
isnull(ltrim(lb.sPrefEmployer), 'No') PrefEmpl,
count(lb.sPrefEmployer) PrefCnt
FROM
property p (nolock)
INNER JOIN tenant t (nolock) on p.hMy = t.hProperty
INNER JOIN Leasebut28 lb (nolock) on t.hMyPerson = lb.hCode
INNER JOIN tenstatus ts (nolock) ON (t.istatus = ts.istatus)
WHERE
(
t.dtMoveIn <= '2022-08-22'
and (
t.dtMoveOut > '2022-08-22'
or t.dtMoveOut is null
)
)
AND ts.Status in ('Current', 'Future', 'Notice')
AND t.sUnitCode NOT IN ('COMAREA', 'WAIT')
AND t.sUnitCode NOT LIKE ('NONRES%')
AND lb.sCorp <> 'Yes'
and p.scode = 'carcen2'
GROUP BY
rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')',
lb.sPrefEmployer
UNION ALL
SELECT
rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')' pscode,
isnull(ltrim(b.sPrefEmployer), 'No') PrefEmpl,
count(b.sPrefEmployer) PrefCnt
FROM
room r (nolock)
INNER JOIN tenant t (nolock) ON (t.hmyperson = r.hmytenant)
INNER JOIN unit u (nolock) ON (u.hmy = t.hunit)
INNER JOIN unittype ut (nolock) on (ut.hMy = u.hUnitType)
INNER JOIN tenstatus ts (nolock) ON (t.istatus = ts.istatus)
INNER JOIN property p (nolock) ON (p.hmy = t.hproperty)
LEFT OUTER JOIN RoomBut1 b (nolock) ON (r.hMyPerson = b.hCode)
WHERE
isnull(r.dtmoveout, '01/01/2400') > Getdate()
AND (
t.dtMoveIn <= '2022-08-22'
and (
t.dtMoveOut > '2022-08-22'
or t.dtMoveOut is null
)
)
AND r.sRelationship <> 'Guarantor'
AND ts.Status in ('Current', 'Future', 'Notice')
AND t.sUnitCode NOT IN ('COMAREA', 'WAIT')
AND t.sUnitCode NOT LIKE ('NONRES%')
AND r.bOccupant <> -1
and p.scode = 'carcen2'
GROUP BY
rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')',
b.sPrefEmployer
) vt
GROUP BY
vt.pscode,
vt.PrefEmpl
Order by 2
I may have misunderstood this but if you just want to get the Yes vs No % share of each pscode then you could just change your query from
SELECT
vt.pscode as pscode,
vt.PrefEmpl as PrefEmpl,
SUM(vt.PrefCnt) as PrefCnt
FROM
(
...
... existing subquery here
...
) vt
GROUP BY
vt.pscode,
vt.PrefEmpl
Order by 2
to
SELECT DISTINCT
vt.pscode as pscode,
vt.PrefEmpl as PrefEmpl,
SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode, vt.PrefEmpl) as PrefCnt
CAST(SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode, vt.PrefEmpl) as float)
/ CAST(SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode) as float)
as PrefCntPercent
FROM
(
...
... existing subquery here
...
) vt
Order by 2