Обсуждение: Calculate the ratio
Hi, Try this select gridyear, gwcode, sum(case when cf=1 then area else o end) as cf1, sum(case when cf=0 then area else 0 end) as cf0 from table group by gridyear, gwcode http://sql-ex.ru/help/select8.php#case 15.08.2011, 16:21, "Andreas Forø Tollefsen" <andreasft@gmail.com>: > Hi all, > > This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column(cf). > > Structure: > > gid; gridyear; gwcode; area; cf > > I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group bygridyear and gwcode. > Then i can see the ratio of the area of each country affected by cf. > > Any suggestions? > > Thanks. > > A.
SELECT cols,
SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1,
SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0
FROM table
WHERE cf IN (0, 1)
GROUP BY cols
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Forø Tollefsen
Sent: Monday, August 15, 2011 8:22 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Calculate the ratio
Hi all,
This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf).
Structure:
gid; gridyear; gwcode; area; cf
I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode.
Then i can see the ratio of the area of each country affected by cf.
Any suggestions?
Thanks.
A.
Replace the “1” in the case with “area”… like the msi77 said
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of David Johnston
Sent: Monday, August 15, 2011 9:08 AM
To: 'Andreas Forø Tollefsen'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Calculate the ratio
SELECT cols,
SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1,
SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0
FROM table
WHERE cf IN (0, 1)
GROUP BY cols
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Forø Tollefsen
Sent: Monday, August 15, 2011 8:22 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Calculate the ratio
Hi all,
This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf).
Structure:
gid; gridyear; gwcode; area; cf
I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode.
Then i can see the ratio of the area of each country affected by cf.
Any suggestions?
Thanks.
A.