Calcuate percentage.

select var1, count(*) from table1 group by var1;

will get you the count of how many of each number there is...

I think the only way is to get the max and manually loop thru the

rows and

calc the percentage.

==>You mean externally using a programming ==>language
==>to do it?

-----Original Message-----
From: Sze Yuen Wong <swong_@yahoo.com>
To: pgsql-general@hub.org <pgsql-general@hub.org>
Date: Tuesday, March 09, 1999 9:48 AM
Subject: [GENERAL] Calcuate percentage.

Hi,

I need to calcuate the percentage from my table:

var1
------
1
1
1
2
2
3

Desire result:

var1 | percentage
----------------------------
1 | 50%
2 | 33%
3 | 17%

===============================

Any clue?

Please help.

Sze Wong

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

select var1, count(*) from table1 group by var1;

will get you the count of how many of each number there is...

I think the only way is to get the max and manually loop thru the rows and
calc the percentage.

-----Original Message-----
From: Sze Yuen Wong <swong_@yahoo.com>
To: pgsql-general@hub.org <pgsql-general@hub.org>
Date: Tuesday, March 09, 1999 9:48 AM
Subject: [GENERAL] Calcuate percentage.

Can I somehow get the total number of rows in
a function?

if so, then I can say:

select var1, count(*) / numRows() * 100 from table1 group by var1;

Sze Wong

---"K.T." wrote:

select var1, count(*) from table1 group by var1;

will get you the count of how many of each number there is...

I think the only way is to get the max and manually loop thru the

rows and

calc the percentage.

-----Original Message-----
From: Sze Yuen Wong <swong_@yahoo.com>
To: pgsql-general@hub.org <pgsql-general@hub.org>
Date: Tuesday, March 09, 1999 9:48 AM
Subject: [GENERAL] Calcuate percentage.

Hi,

I need to calcuate the percentage from my table:

var1
------
1
1
1
2
2
3

Desire result:

var1 | percentage
----------------------------
1 | 50%
2 | 33%
3 | 17%

===============================

Any clue?

Please help.

Sze Wong

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

select count(*) into t2 from stations;
FATAL 1: btree: failed to add item to the right sibling

Any clue?

---Marcin Grondecki wrote:

select count (*) into t1 from t;
select count(varl), varl into t2 from t group by varl;
select varl, (t2.count*100/t1.count) from t2, t1;

and, of coz, drop tables t1 'n' t2 ;)
(i don't know it's my laminess, but syntax "select ... into TEMP ddd

...

does'n work for me - maybe suggestions? a bug i don't know about?)

At 06:54 99-03-09 -0800, you wrote:

Hi,

I need to calcuate the percentage from my table:

var1
------
1
1
1
2
2
3

Desire result:

var1 | percentage
----------------------------
1 | 50%
2 | 33%
3 | 17%

===============================

Any clue?

Please help.

Sze Wong

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

Marcin Grondecki
ojciec@mtl.pl
+48(604)468725
***** I'm not a complete idiot, some parts are missing...

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

Can I somehow get the total number of rows in
a function?

create function numRows() returns int4
as 'select count(*) from <table>'
language 'sql';

select var1, count(*) / numRows() * 100 from table1 group by var1;

maybe this is better

select var1, (count(*) * 100) / numRows() from table1 group by var1;

As I tried to make a function for this percent calculation I fell over
the problem of passing a table to the function. I'd like to make this
function:

create function NumRows(table) returns int4
as 'select count(*) from $1'
language 'sql';

Is this possible?

It's crude and not very efficient, but here is a solution:

CREATE TABLE temp ( var INT2 );
INSERT INTO temp VALUES (1);
etc.

CREATE FUNCTION temp_row_count() RETURNS FLOAT AS
'SELECT COUNT(*)::FLOAT AS result FROM temp'
LANGUAGE 'sql';

SELECT var, COUNT(*)::FLOAT / temp_row_count() AS pct
FROM temp GROUP BY var;

Hope this helps,

Clark

P.S. In oracle, I'd use a sub-query:

SELECT var, COUNT(*) / total_count
FROM temp,
( SELECT COUNT(*) AS total_count
FROM temp
)
GROUP BY var;

Sze Yuen Wong wrote:

select count (*) into t1 from t;
select count(varl), varl into t2 from t group by varl;
select varl, (t2.count*100/t1.count) from t2, t1;

and, of coz, drop tables t1 'n' t2 ;)
(i don't know it's my laminess, but syntax "select ... into TEMP ddd ...
does'n work for me - maybe suggestions? a bug i don't know about?)

At 06:54 99-03-09 -0800, you wrote:

Hi,

I need to calcuate the percentage from my table:

var1
------
1
1
1
2
2
3

Desire result:

var1 | percentage
----------------------------
1 | 50%
2 | 33%
3 | 17%

===============================

Any clue?

Please help.

Sze Wong

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

Marcin Grondecki
ojciec@mtl.pl
+48(604)468725
***** I'm not a complete idiot, some parts are missing...

P.S. In oracle, I'd use a sub-query:

SELECT var, COUNT(*) / total_count
FROM temp,
( SELECT COUNT(*) AS total_count
FROM temp
)
GROUP BY var;

I thought that subqueries were allowed in PostgreSQL after 6.2?