Re: 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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Andreas Forø Tollefsen
Sent: Monday, August 15, 2011 8:22 AM
To: pgsql-sql(at)postgresql(dot)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.