Received: from israel.diaspora.gen.nz (mail@israel.diaspora.gen.nz [202.50.109.65]) by hub.org (8.10.1/8.10.1) with ESMTP id e6F5VR036734 for ; Sat, 15 Jul 2000 01:31:28 -0400 (EDT) Received: from rodgerd by israel.diaspora.gen.nz with local (Exim 3.12 #1 (Debian)) id 13DKXt-0001Vo-00; Sat, 15 Jul 2000 17:31:25 +1200 Date: Sat, 15 Jul 2000 17:31:25 +1200 To: pgsql-sql@postgresql.org Subject: Applying SUM twice in the same query. Message-ID: <20000715173124.A5793@diaspora.gen.nz> Reply-To: rodgerd@diaspora.gen.nz Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2i From: Rodger Donaldson X-Archive-Number: 200007/154 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