Calculating and reporting percentages as part of a rollup

Posted on 2003-03-20
Medium Priority
Last Modified: 2012-08-13
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'
FROM colour_table
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?

Mark Batten-Carew
Question by:markbc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 13

Expert Comment

ID: 8177160
select x.Province, x.City, x.Colour,'Percentage Preferring This Colour'=convert(float,x.Cnt)*100/a.AllCnt
 SELECT   Province, City, Colour, count(*) as Cnt
 FROM colour_table
 GROUP BY Province, City, Colour WITH ROLLUP
) x
cross join ( select count(*) as AllCnt from colour_table ) a
ORDER BY Province, City, Colour
LVL 13

Expert Comment

ID: 8177173
I used float, because I don't know, how many records are in the table colour_table. You can replace by numeric datatype or a converting UDF.

Good luck !

Author Comment

ID: 8178263
Sorry, I meant to cancel this question minutes after I submitted it, but was not able to get to a computer until now.  I will try out your idea, but I am probably going to solve this by calculating the percentages as I reformat the SQL output into a pretty report.
LVL 13

Accepted Solution

ispaleny earned 200 total points
ID: 8178462
I see one problem. You posted
"...the total people who prefer each colour in each city, province and overall country."

And you used
GROUP BY Province, City    , Colour WITH ROLLUP
But it will return
( Province, City, Colour )
( Province, City )
( Province )
( ALL )

You need
GROUP BY Colour,   Province, City   WITH ROLLUP
( Colour, Province, City )
( Colour, Province )
( Colour )
( ALL )

Am I right ?


Author Comment

ID: 8265071
My problem was actually support per candidate within a political riding rolled up across the country.  Your answer did lead me fairly quickly to my final version of this question, which I have included below in case anyone else finds this question useful.  But, in the end, I found it more useful to just do a simple query WITH CUBE, and then post process the output, calculating the percentages as I reformatted the output for HTML.

The answer with SQL calculating the percentages was:
SELECT a.Region, a.Riding, a.First_Choice, a.Support, b.RidingTotal, c.RegionTotal,
(SELECT count(*) from delegate_info) as CanadaTotal,
'Riding %'=a.Support*100/b.RidingTotal,
'Region %'=a.Support*100/c.RegionTotal
SELECT Region, Riding, First_Choice, count(*) as 'Support'
FROM delegate_info
group by Region, Riding, First_Choice with cube
) AS a
SELECT Region, Riding, count(*) as RidingTotal
from delegate_info
group by Region, Riding
) as b on a.Region = b.Region and a.Riding = b.Riding
SELECT Region, count(*) as RegionTotal
from delegate_info
group by Region
) as c on a.Region = c.Region
ORDER BY a.Region, a.Riding, a.First_Choice

The answer without SQL doing the calculations that I actually am using is:

SELECT Region, Riding, First_Choice, 'Support'=COUNT(*)
FROM party_members
GROUP BY Region, Riding, First_Choice WITH CUBE
ORDER BY Region, Riding, 'Support' DESC, First_Choice

This gives me First_Choice broken down by everything I wanted, plus because I sort count descending, I get the total first, and can calculate percentages for each following line.

Thank you very much for pointing me in the right direction, though.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question