Percent of Total Query Help

Percent of Total Query Help

Post by ZZZYY » Tue, 06 Nov 2001 01:19:54



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),
IPAddress varchar (16),
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

Post by lindawi » Tue, 06 Nov 2001 02:07:16


ZZZYYY,

How about something like this:

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),
> IPAddress varchar (16),
> 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

Post by Dan Guzma » Tue, 06 Nov 2001 02:08:00


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),
> IPAddress varchar (16),
> 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')

 
 
 

1. OLAP Calculated member for percent of total revenue.

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

2. PL/SQL returning abnormalities????

3. MDX:Acumulated percent or total

4. CDaorecordset::AddNew

5. Calculated member - Percent of total

6. delete a table on the fly?

7. SQL: Percent of total count

8. Drop Down List Question (And Tray Question)

9. percent of total in a GROUP BY

10. Percent of total

11. Percent of Total in Crystal 4.5 Cross Tab Report

12. SQL question: figuring percents on totals

13. Percent column working at break key totals - How