Applying SUM twice in the same query.
От | Rodger Donaldson |
---|---|
Тема | Applying SUM twice in the same query. |
Дата | |
Msg-id | 20000715173124.A5793@diaspora.gen.nz обсуждение исходный текст |
Ответы |
Re: Applying SUM twice in the same query.
|
Список | 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
В списке pgsql-sql по дате отправления: