## Percent of Total Query Help

### Percent of Total Query Help

I'm trying to get a percent of total for each item and having a little
trouble in my lack of SQL experience. Below is the DML.  I'm trying to get
the percent of total for each distinct port, and am expecting my results to
look like:

Port        Percent

23            .2222
80            .4444
110           .1111
443           .2222

So, out of a total of 9 entries, Port 23 occurs 22.222% of the time, and so
forth.

ZZZYYY

create table Test
(
Test_id int identity (1,1),
Port int,
Creation_time datetime
)
go
INSERT INTO test VALUES ('192.168.1.100', '80',  '10/01/2001')
INSERT INTO test VALUES ('192.168.1.100', '23',  '10/01/2001')
INSERT INTO test VALUES ('192.168.1.100', '80',  '10/02/2001')
INSERT INTO test VALUES ('192.168.1.200', '23',  '10/02/2001')
INSERT INTO test VALUES ('192.168.1.200', '80',  '10/02/2001')
INSERT INTO test VALUES ('192.168.1.200', '110', '10/03/2001')
INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
INSERT INTO test VALUES ('192.168.1.300', '80',  '10/04/2001')

### Percent of Total Query Help

ZZZYYY,

select Port, cast(count(*) / N as decimal (8,4)) 'Percent'
from Test
cross join (select cast(count(*) as float) N from Test) Total
group by Port, N

Port        Percent
----------- ----------
23          .2222
80          .4444
110         .1111
443         .2222

Linda

Quote:> I'm trying to get a percent of total for each item and having a little
> trouble in my lack of SQL experience. Below is the DML.  I'm trying to get
> the percent of total for each distinct port, and am expecting my results
to
> look like:

> Port        Percent

> 23            .2222
> 80            .4444
> 110           .1111
> 443           .2222

> So, out of a total of 9 entries, Port 23 occurs 22.222% of the time, and
so
> forth.

> Thanks so much in advance!
> ZZZYYY

> create table Test
> (
> Test_id int identity (1,1),
> Port int,
> Creation_time datetime
> )
> go
> INSERT INTO test VALUES ('192.168.1.100', '80',  '10/01/2001')
> INSERT INTO test VALUES ('192.168.1.100', '23',  '10/01/2001')
> INSERT INTO test VALUES ('192.168.1.100', '80',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '23',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '80',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '110', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '80',  '10/04/2001')

### Percent of Total Query Help

One method:

SELECT Port int,
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Test)
FROM Test
GROUP BY Port

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

Quote:> I'm trying to get a percent of total for each item and having a little
> trouble in my lack of SQL experience. Below is the DML.  I'm trying to get
> the percent of total for each distinct port, and am expecting my results
to
> look like:

> Port        Percent

> 23            .2222
> 80            .4444
> 110           .1111
> 443           .2222

> So, out of a total of 9 entries, Port 23 occurs 22.222% of the time, and
so
> forth.

> Thanks so much in advance!
> ZZZYYY

> create table Test
> (
> Test_id int identity (1,1),
> Port int,
> Creation_time datetime
> )
> go
> INSERT INTO test VALUES ('192.168.1.100', '80',  '10/01/2001')
> INSERT INTO test VALUES ('192.168.1.100', '23',  '10/01/2001')
> INSERT INTO test VALUES ('192.168.1.100', '80',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '23',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '80',  '10/02/2001')
> INSERT INTO test VALUES ('192.168.1.200', '110', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '443', '10/03/2001')
> INSERT INTO test VALUES ('192.168.1.300', '80',  '10/04/2001')

I'm trying to add a calculated member to an OLAP cube which has a Revenue
measure, along with Client and Account Executive dimensions. What I'm trying
to create is the percentage of total revenue.

The following calculation works when the Account Executive dimension is
included in the browsing area:

([Measures].[Revenue]/Max([Account_Executive].members,
[Measures].[Revenue]))*100

I'd like the calculation to work no matter what dimension is included
however. For example, if I replace the Account Executive dimension with
Client, I'd like this caclulation to reflect the percentage of each client's
revenue, in relation to the total revenue.

This seems like it should be pretty straight forward, but I'm having trouble
getting it to work properly. Anybody have any ideas?

Kevin Grossman