Calculating and reporting percentages as part of a rollup
Posted on 2003-03-20
I have a large base of data like:
Name Province City Colour
Mark Ontario Ottawa Red
John Ontario Kingston Red
Mary BC Vancouver Green
and so on
There is a small list of colour choices (eg. red, orange, yellow, blue, green) and I would like to get a report of the NUMBER and PERCENTAGE of the total people who prefer each colour in each city, province and overall country.
The NUMBER is easy to get using rollup. But how do I get what PERCENTAGE that number is of the group it is a part of.
In other words, for each line of the rollup:
SELECT Province, City, Colour, count(*) as 'Total Preferring This Colour'
GROUP BY Province, City, Colour WITH ROLLUP
ORDER BY Province, City, Colour
I would like to be able to print out what PERCENTAGE each line is of the next higher rollup.
Hint - I am looking for a calculated field to add after count(*) which will be called PERCENTAGE. Something like "count(*)/GROUPTOTAL*100"
Is that clear enough?