I'm running the following Query:
select isnull(right(araccltid,4),'Tot') as ProdCode, count(aracid) as NumofAccounts from araccount where right(araccltid,4) between 5109 and 5112 group by right(araccltid,4) with rollup
using winSQL and it returns:
ProdCode NumofAccounts
5109 21864
5110 4206
5111 7380
5112 40075
Tot 73525
I would like to add a percentage column so that I see this:
ProdCode NumofAccounts Percentage
5109 21864 29.74
5110 4206 5.72
5111 7380 10.04
5112 40075 54.51
Tot 73525 100.00
I've tried adjusting the script as such:
select isnull(right(araccltid,4),'Tot') as ProdCode, count(aracid) as NumofAccounts, count(aracid)/tblCount.AcctCount as Percentage from araccount, (select count(aracid) as AcctCount from araccount where right(araccltid,4) between 5109 and 5112) as tblCount where right(araccltid,4) between 5109 and 5112 group by right(araccltid,4) with rollup
but I get this error:
Error: Column 'tblCount.AcctCount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (State:37000, Native Code: 1FB8)
Any help?