pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Applying SUM twice in the same query.
2+ messages / 2 participants
[nested] [flat]

* Applying SUM twice in the same query.
@ 2000-07-15 05:31  Rodger Donaldson <rodgerd@diaspora.gen.nz>
  0 siblings, 1 reply; 2+ messages in thread

From: Rodger Donaldson @ 2000-07-15 05:31 UTC (permalink / raw)
  To: pgsql-sql

I have a table structured:

server_name        |server_version  |number
-------------------+----------------+------
Apache             |1.3.11          |     1
Netscape-Enterprise|2.01            |     1
Apache             |1.1.3           |     2

Initially I wanted to extract the total count by server_name; this is pretty
straightforward:

select server_name, sum(number) 
from web_servers 
group by server_name;

...gives the result I expect.

server_name        |sum
-------------------+---
Apache             | 64
Draupnir           |  1

I also want the total number of of servers.  This can be obtained with:

select sum(number) 
from web_servers;

Again, works fine.  What I really want to do with total is to derive a
proportion of number of server:number of servers, something like:

server_name        |number|total
-------------------+------+-----
AiNET Apache       |     1|   84
Apache             |    64|   84
Draupnir           |     1|   84

The problem is, I can't forumlate a query to produce it.  The closest I got
was:

SELECT ws1.server_name, SUM(ws1.number) AS number, SUM(ws2.number) AS total
FROM web_servers AS ws1, web_servers AS ws2 
GROUP BY ws1.server_name;

...which is producing:

server_name        |number|total
-------------------+------+-----
AiNET Apache       |    19|   84
Apache             |  1216|  840
Draupnir           |    19|   84

I assume I'm creating a product of the query, but I'm not sure how to fix it.

-- 
Rodger Donaldson		rodgerd@diaspora.gen.nz
Driving that fast may also be an autoLART because the acceleration required
to reach a high fraction of c in just a few kilometres may be difficult to
withstand.  You will also need a better fuel than gasoline. 
		-- Steve VanDevender



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Applying SUM twice in the same query.
@ 2000-07-15 15:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rodger Donaldson <rodgerd@diaspora.gen.nz>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-07-15 15:59 UTC (permalink / raw)
  To: rodgerd@diaspora.gen.nz; +Cc: pgsql-sql

Rodger Donaldson <rodgerd@diaspora.gen.nz> writes:
> What I really want to do with total is to derive a
> proportion of number of server:number of servers, something like:

Only way to do that is with a sub-select, because all the aggregates
appearing in a single query are going to be computed over the same
input set(s).  Try

select
	server_name,
	sum(number) AS number,
	(select sum(number) from web_servers) AS total
from web_servers 
group by server_name;

While this looks like it might be inefficient, it's really not because
Postgres recognizes that the inner select is independent of the outer
and only evaluates it once.

Note you need PG 7.0 or later to do this.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-07-15 15:59 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-15 05:31 Applying SUM twice in the same query. Rodger Donaldson <rodgerd@diaspora.gen.nz>
2000-07-15 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>

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