Обсуждение: 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

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

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

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