March 20, 2008 at 1:20 pm
I have the following query.
SELECT COUNT(CITY) AS MEMBERCITY, CITY
FROM MR_MEMBER_1
GROUP BY CITY
ORDER BY COUNT(CITY) DESC
I get two coulmns on the report City and Count. I am trying to show the sum
of all the counts at the bottom of the report and show percentage in another
column next to count column that would show the percentage of one particular
city count from the total sum.
City Count Percentage
ABC 1000 % of SUM
XYZ 500 % of SUM
ETC 1500 % of SUM
SUM = 3000 How can I do this. Thanks in advance.
March 20, 2008 at 2:19 pm
Right click your row, insert / make sure you have a table footer.
The Membercity column footer should be:
=Sum(Fields!MEMBERCITY.Value)
Right click your second column (CITY), insert a column to the right, call it PCT (or whatever).
The value for that third cell in the table row should be:
=Fields!MEMBERCITY.Value/SUM(Fields!MEMBERCITY.Value)
You can then right click the cell, go to properties-->format, and pick Percentage format code.
Have fun!
March 20, 2008 at 2:25 pm
Use a stored procedure, like this:
DECLARE @Total float
SELECT @Total = Cast(COUNT(CITY) as float) FROM MR_MEMBER_1
SELECT COUNT(CITY) AS MEMBERCITY, CITY
, 100 * Cast(COUNT(CITY) as float)/@Total AS PERCENTAGE
FROM MR_MEMBER_1
GROUP BY CITY
ORDER BY COUNT(CITY) DESC
Depending on how you format your report, you may or may not need the "[font="Courier New"]100 * [/font]" factor in front of the percentage calculation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 3:00 pm
Great It works like a charm. Thank you very much.
Right click your row, insert / make sure you have a table footer.
The Membercity column footer should be:
=Sum(Fields!MEMBERCITY.Value)
Right click your second column (CITY), insert a column to the right, call it PCT (or whatever).
The value for that third cell in the table row should be:
=Fields!MEMBERCITY.Value/SUM(Fields!MEMBERCITY.Value)
You can then right click the cell, go to properties-->format, and pick Percentage format code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply