Обсуждение: Applying SUM twice in the same query.

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

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

selectserver_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