Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

580 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