pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcalculating percentages
13+ messages / 8 participants
[nested] [flat]
* calculating percentages
@ 1999-07-12 13:50 Thomas Good <tomg@admin.nrnet.org>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Good @ 1999-07-12 13:50 UTC (permalink / raw)
To: pgsql-sql
Hi.
I am trying to do outcome studies on pt before and after treatment
recidivism rates...and having some trouble calculating percentages.
How does one do this: SELECT (current_value/target) * 100;
in postgres?
Cheers - and thanks,
Tom
------- North Richmond Community Mental Health Center -------
Thomas Good MIS Coordinator
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-12 14:50 Thomas Good <tomg@nrnet.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Thomas Good @ 1999-07-12 14:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Good <tomg@admin.nrnet.org>; pgsql-sql
On Mon, 12 Jul 1999, Tom Lane wrote:
> Thomas Good <tomg@admin.nrnet.org> writes:
> > How does one do this: SELECT (current_value/target) * 100;
> > in postgres?
> Pretty much just like that. What problem are you having?
>
> regards, tom lane
The divison rtns 0, which multiplied by 100, rtns 0.
It's neat. But not quite the dramatic proof (of the efficacy
of our treatment) that my superiors are looking for!
------- North Richmond Community Mental Health Center -------
Thomas Good MIS Coordinator
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-12 14:53 Tiberiu Craciun <tiberiu@akela.ro>
0 siblings, 0 replies; 13+ messages in thread
From: Tiberiu Craciun @ 1999-07-12 14:53 UTC (permalink / raw)
To: pgsql-sql
There is an example :
create table values ( value float ) ;
create function sum_val() returns float as '
select sum(value) as return from values;
' language 'sql';
..........
select (value/sum_val())*100 as percent from values;
if value is integer than
create table values ( value int2 ) ;
create function sum_val() returns int2 as '
select sum(value) as return from values;
' language 'sql';
..........
select (value::float/sum_val()::float)*100 as percent from values;
-----Original Message-----
From: Thomas Good <tomg@admin.nrnet.org>
To: PostgreSQL::SQL List <pgsql-sql@postgreSQL.org>
Date: lundi 12 juillet 1999 22:31
Subject: [SQL] calculating percentages
>Hi.
>
>I am trying to do outcome studies on pt before and after treatment
>recidivism rates...and having some trouble calculating percentages.
>
>How does one do this: SELECT (current_value/target) * 100;
>in postgres?
>
>Cheers - and thanks,
>Tom
>
>------- North Richmond Community Mental Health Center -------
>
>Thomas Good MIS Coordinator
>Vital Signs: tomg@ { admin | q8 } .nrnet.org
> Phone: 718-354-5528
> Fax: 718-354-5056
>
>/* Member: Computer Professionals For Social Responsibility */
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-12 15:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Good <tomg@admin.nrnet.org>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 1999-07-12 15:06 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql
Thomas Good <tomg@admin.nrnet.org> writes:
> How does one do this: SELECT (current_value/target) * 100;
> in postgres?
Pretty much just like that. What problem are you having?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-12 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Good <tomg@nrnet.org>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 1999-07-12 16:57 UTC (permalink / raw)
To: Thomas Good <tomg@nrnet.org>; +Cc: pgsql-sql
Thomas Good <tomg@nrnet.org> writes:
> On Mon, 12 Jul 1999, Tom Lane wrote:
>> Pretty much just like that. What problem are you having?
> The divison rtns 0, which multiplied by 100, rtns 0.
If your data is stored as int fields, then you'd get integer division,
which sounds like what you're getting. You want a floating-point
division, which you'd get by promoting to float:
SELECT (current_value::float8/target::float8) * 100;
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-12 16:57 Thomas Good <tomg@admin.nrnet.org>
0 siblings, 0 replies; 13+ messages in thread
From: Thomas Good @ 1999-07-12 16:57 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-sql
On Mon, 12 Jul 1999, Rich Shepard wrote:
> > How does one do this: SELECT (current_value/target) * 100;
> > in postgres?
>
> Tom,
>
> In postgres or anything else, I'd select the records I wanted first, then
> assign each percentage to a variable; e.g.,
>
> pct_chng = (current_value/target) * 100;
>
> The varible, pct_chng, can be inserted in a view as another column and
> statistics can be calculated from these values.
>
> Rich
Rich,
I believe you hit the nail on the head...it's Monday don't you know
and the brain wasn't plugged in. I generally embed sql in perl and
can't imagine why it didn't occur to me to let perl's arithmetic
operators do the work on the values returned from pg.
I do alot of one-off statement handles ($sth->$fetchrow_array) where
the return value is an aggregate (so although the syntax is for a cursor
the stanza becomes a defacto singleton select). Then I let perl
do the rest, example (to suggest a startdate to a user):
$sth_timer = $dbh->prepare("
SELECT current_date - 30;
");
$sth_timer->execute or die;
$start_date = $sth_timer->fetchrow_array;
print("Start Date = $start_date? (Y/n): ");
Obviously, I'm an incurable shell scripter - but it does work!
Of course, I'll try the view you attached, and the function
another colleague sent but perl is probably what I'll use in the end.
Thanks for the wake up call!
Cheers,
Tom
------- North Richmond Community Mental Health Center -------
Thomas Good MIS Coordinator
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-13 12:52 D'Arcy J.M. Cain <darcy@druid.net>
parent: Thomas Good <tomg@nrnet.org>
1 sibling, 1 reply; 13+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-07-13 12:52 UTC (permalink / raw)
To: tomg@nrnet.org; +Cc: tgl@sss.pgh.pa.us; tomg@admin.nrnet.org; pgsql-sql
Thus spake Thomas Good
> > > How does one do this: SELECT (current_value/target) * 100;
> The divison rtns 0, which multiplied by 100, rtns 0.
Does "SELECT (current_value * 100)/target" work better?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] calculating percentages
@ 1999-07-13 13:30 Thomas Good <tomg@admin.nrnet.org>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 13+ messages in thread
From: Thomas Good @ 1999-07-13 13:30 UTC (permalink / raw)
To: pgsql-sql; +Cc: tgl@sss.pgh.pa.us
On Tue, 13 Jul 1999, D'Arcy J.M. Cain wrote:
> Thus spake Thomas Good
> > > > How does one do this: SELECT (current_value/target) * 100;
> > The divison rtns 0, which multiplied by 100, rtns 0.
>
> Does "SELECT (current_value * 100)/target" work better?
Darcy,
SELECT (2 * 100)/3;
?column?
----------------
66
(1 row)
This is clean and I don't have to truncate the value in the end.
Thanks!
BTW, I noticed an idiosnycratic behaviour that I don't really
care for when doing floating point calculations:
SELECT (2::float8/3::float8) * 100;
?column?
----------------
66.6666666666667 <----------------- I don't need to round up here.
(1 row)
EOF
So, Larry W comes in handy again:
#!/usr/bin/perl
$val = (2/3) * 100;
print("$val\n");
66.6666666666666
------- North Richmond Community Mental Health Center -------
Thomas Good MIS Coordinator
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Calculating Percentages
@ 2003-05-08 15:02 David Siegal <dsiegal@brave.cs.uml.edu>
0 siblings, 2 replies; 13+ messages in thread
From: David Siegal @ 2003-05-08 15:02 UTC (permalink / raw)
To: pgsql-sql
I have 'member' table with a 'nationality' column. I want to get the
percentage breakdown of members by nationality, e.g.
American 29%
Canadian 14%
Mexican 11%
...
Is there an efficient way to do this is a single query?
If I were to do:
SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
percentage FROM member GROUP BY nationality ORDER BY nationality;
would this repeatedly execute the inner query over and over?
Thanks!
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Calculating Percentages
@ 2003-05-14 15:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Siegal <dsiegal@brave.cs.uml.edu>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2003-05-14 15:23 UTC (permalink / raw)
To: David Siegal <dsiegal@brave.cs.uml.edu>; +Cc: pgsql-sql
David Siegal <dsiegal@brave.cs.uml.edu> writes:
> If I were to do:
> SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
> percentage FROM member GROUP BY nationality ORDER BY nationality;
> would this repeatedly execute the inner query over and over?
No, it'd do it just once, because the sub-select has no dependency on
the outer select. (If the sub-select used any variables from the outer
level, then it'd have to be done over at each outer row.)
You can tell the difference between these cases by looking at EXPLAIN
output for the query: do-once sub-selects are labeled InitPlans,
do-every-time sub-selects are labeled SubPlans.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Calculating Percentages
@ 2003-05-14 15:24 Josh Berkus <josh@agliodbs.com>
parent: David Siegal <dsiegal@brave.cs.uml.edu>
1 sibling, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2003-05-14 15:24 UTC (permalink / raw)
To: David Siegal <dsiegal@brave.cs.uml.edu>; +Cc: pgsql-sql
David,
> If I were to do:
> SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
> percentage FROM member GROUP BY nationality ORDER BY nationality;
>
> would this repeatedly execute the inner query over and over?
Yes, it would
Better is:
SELECT nationality, (COUNT(*)*100/total_members) as percentage
FROM member,
(SELECT COUNT(*) as total_members FROM members) tot_mem
GROUP BY nationality
ORDER BY nationality
This method runs the grand total only once.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Calculating Percentages
@ 2003-05-15 09:04 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 13+ messages in thread
From: Christoph Haller @ 2003-05-15 09:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: dsiegal@brave.cs.uml.edu
>
> SELECT nationality, (COUNT(*)*100/total_members) as percentage
> FROM member,
> (SELECT COUNT(*) as total_members FROM members) tot_mem
> GROUP BY nationality
> ORDER BY nationality
>
You'll have to GROUP BY nationality, total_members
Remember COUNT returns bigint, so percentage is the
result of an integer division. To obtain floating point results use
(COUNT(*)*100.0/total_members)
> > If I were to do:
>
> > SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member))
as
> > percentage FROM member GROUP BY nationality ORDER BY nationality;
>
> > would this repeatedly execute the inner query over and over?
>
> No, it'd do it just once, because the sub-select has no dependency on
> the outer select. (If the sub-select used any variables from the
outer
> level, then it'd have to be done over at each outer row.)
>
Really nice piece of analyzer.
Regards, Christoph
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Calculating Percentages
@ 2003-05-15 15:42 Josh Berkus <josh@agliodbs.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2003-05-15 15:42 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; +Cc: dsiegal@brave.cs.uml.edu
Chris,
> You'll have to GROUP BY nationality, total_members
Oops. Problem with posting untested SQL ...
> > > would this repeatedly execute the inner query over and over?
> >
> > No, it'd do it just once, because the sub-select has no dependency on
> > the outer select. (If the sub-select used any variables from the
That's great, Tom. Yet another place where we've surpassed MS SQL Server in
query implementation.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-05-15 15:42 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-12 13:50 calculating percentages Thomas Good <tomg@admin.nrnet.org>
1999-07-12 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-12 14:50 ` Thomas Good <tomg@nrnet.org>
1999-07-12 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-13 12:52 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-07-13 13:30 ` Thomas Good <tomg@admin.nrnet.org>
1999-07-12 14:53 Re: [SQL] calculating percentages Tiberiu Craciun <tiberiu@akela.ro>
1999-07-12 16:57 Re: [SQL] calculating percentages Thomas Good <tomg@admin.nrnet.org>
2003-05-08 15:02 Calculating Percentages David Siegal <dsiegal@brave.cs.uml.edu>
2003-05-14 15:23 ` Re: Calculating Percentages Tom Lane <tgl@sss.pgh.pa.us>
2003-05-14 15:24 ` Re: Calculating Percentages Josh Berkus <josh@agliodbs.com>
2003-05-15 09:04 Re: Calculating Percentages Christoph Haller <ch@rodos.fzk.de>
2003-05-15 15:42 ` Re: Calculating Percentages Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox